drop table t purge;
create table t (ID INT, val1 int, val2 int, val3 int);
INSERT INTO t(ID,val1,val2,val3) VALUES (1,10,20,30);
INSERT INTO t(ID,val1,val2,val3) VALUES (1,11,21,31);
INSERT INTO t(ID,val1,val2,val3) VALUES (1,12,22,32);
INSERT INTO t(ID,val1,val2,val3) VALUES (2,11,21,31);
INSERT INTO t(ID,val1,val2,val3) VALUES (2,30,40,50);
INSERT INTO t(ID,val1,val2,val3) VALUES (3,30,40,50);
INSERT INTO t(ID,val1,val2,val3) VALUES (3,41,42,52);
INSERT INTO t(ID,val1,val2,val3) VALUES (4,30,40,50);
INSERT INTO t(ID,val1,val2,val3) VALUES (4,41,42,52);
commit;
demo@ORA12C> select * from t;
ID VAL1 VAL2 VAL3
---------- ---------- ---------- ----------
1 10 20 30
1 11 21 31
1 12 22 32
2 11 21 31
2 30 40 50
3 30 40 50
3 41 42 52
4 30 40 50
4 41 42 52
9 rows selected.
In the above example, only ID=3 and ID=4 are same. The ID=3 has two rows and ID=4 has also got 2 rows. Other than ID column, all other column values has to match.
Here is one approach i had, any other alternatives to this?
demo@ORA12C> select id
2 from (
3 select id,val1_list,val2_list,val3_list,
4 count(*) over( partition by val1_list,val2_list,val3_list ) cnt
5 from (
6 select id, listagg(val1,',') within group(order by val1) val1_list,
7 listagg(val2,',') within group(order by val1) val2_list,
8 listagg(val3,',') within group(order by val1) val3_list
9 from t
10 group by id
11 )
12 )
13 where cnt > 1
14 /
ID
----------
3
4