Clever Geek Handbook

Oracle sql create combinations of the same table without repeating

How can I solve this? I need to eliminate duplicate cartesian product, table with itself. I was thinking of using "connect by". Thank.

create table foo (
num number(2)
);

insert into foo values (1);
insert into foo values (2);
insert into foo values (3);

select a.num,b.num
from foo a, foo b;

NUM NUM
--- ---
 1   1 
 1   2 
 1   3 
 2   1 * duplicated
 2   2 
 2   3 
 3   1 * duplicated
 3   2 * duplicated
 3   3
+3


source to share


4 answers


You can try this:



select a.num,b.num
  from foo a cross join foo b
 where a.num <= b.num
+2


source 


select a.num,b.num
from foo a, foo b
where a.num = b.num


+1


source


SQL Fiddle

Oracle 11g R2 schema setup :

create table foo ( num ) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;

Request 1 :

select a.num,b.num
from foo a CROSS JOIN foo b
WHERE a.num <= b.num

Results :

| NUM | NUM |
|-----|-----|
|   1 |   1 |
|   1 |   2 |
|   1 |   3 |
|   2 |   2 |
|   2 |   3 |
|   3 |   3 |
+1


source


There is no need for a cross join because you are doing a join where you want to match rows to the second table instance that are the same or greater than the number from the first table instance. Here's how you prevent "duplicate" entries:

with foo as (SELECT LEVEL num FROM DUAL CONNECT BY LEVEL <= 3)
select f1.num f1_num,
       f2.num f2_num
from   foo f1
       join foo f2 on (f1.num <= f2.num);

    F1_NUM     F2_NUM
---------- ----------
         1          1
         1          2
         1          3
         2          2
         2          3
         3          3
+1


source






More articles:


All Articles