Oracle: find duplicate rows in select query

Question

My SQL query returns results with 4 columns "A", "B", "C", "D".

Suppose the results are:

A    B    C    D
1    1    1    1
1    1    1    2
2    2    2    1

Is it possible to get the count of duplicate rows with columns "A", "B", "C" in each row.

e.g. the expected result is:

A    B    C    D    cnt
1    1    1    1    2
1    1    1    2    2
2    2    2    1    1

I tried using count(*) over. But it returns me the total number of rows returned by the query. Another information is that in example I have mentioned only 3 columns based on which I need to check the count. But my actual query has such 8 columns. And number of rows in database are huge. So I think group by will not be a feasible option here. Any hint is appreciable. Thanks.

score 9 · Accepted Answer · answered Jan 18 '13 at 15:07

Maybe too late, but probably the count over as analytic function (aka window function) within oracle helps you. When I understand your request correctly, this should solve your problem :

create table sne_test(a number(1)
                 ,b number(1)
                 ,c number(1)
                 ,d number(1)
                 ,e number(1)
                 ,f number(1));

insert into sne_test values(1,1,1,1,1,1);
insert into sne_test values(1,1,2,1,1,1);
insert into sne_test values(1,1,2,4,1,1);
insert into sne_test values(1,1,2,5,1,1);
insert into sne_test values(1,2,1,1,3,1);
insert into sne_test values(1,2,1,2,1,2);
insert into sne_test values(2,1,1,1,1,1);

commit;

 SELECT a,b,c,d,e,f, 
       count(*) over (PARTITION BY a,b,c)
  FROM sne_test;

 A  B  C  D  E  F AMOUNT
-- -- -- -- -- -- ------
 1  1  1  1  1  1      1
 1  1  2  4  1  1      3
 1  1  2  1  1  1      3
 1  1  2  5  1  1      3
 1  2  1  1  3  1      2
 1  2  1  2  1  2      2
 2  1  1  1  1  1      1

This also worked with Oracle 12c. I can take a nap now. – James Jun 17 '17 at 19:21 — James, Jun 17 '17 at 19:21

score 6 · Answer 2 · edited Jan 18 '19 at 11:28

6

To find duplicates you must group the data based on key column

select 
  count(*)
  ,empno 
from 
  emp
group by
  empno
having
  count(*) > 1;

This allows you to aggregate by empno even when multiple records exist for each category (more than one).

edited Jan 18 '19 at 11:28

Community

1
1

answered Sep 06 '14 at 06:35

surendra

61
1
1

1

Hello Surendra, I can not use group by as my select query fetches more that 20 columns and there are 8 such columns on whom i would have to use group by. Please refer to my comment on answer given by fthiella. – user613114 Sep 12 '14 at 06:19

fthiella · Answer 3 · 2012-12-08T09:23:34.663

3

You have to use a subquery where you get the count of rows, grouped by A, B and C. And then you join this subquery again with your table (or with your query), like this:

select your_table.A, your_table.B, your_table.C, your_table.D, cnt
from
  your_table inner join
  (SELECT A, B, C, count(*) as cnt
   FROM your_table
   GROUP BY A, B, C) t
  on t.A = your_table.A
     and t.B = your_table.B
     and t.C = your_table.C

edited Dec 08 '12 at 09:23

answered Dec 08 '12 at 09:18

fthiella

48,073
15
90
106

I can not use group by as my select query fetches more that 20 columns and there are 8 such columns on whom i would have to use group by. So I believe it will hit the performance. – user613114 Dec 08 '12 at 09:35
@user613114 without `group by`.... well maybe you could use a query with variables (i know how to do it in mysql but not in oracle) but you still need to order by your 8 columns... and i don't believe it's faster than using a group by – fthiella Dec 08 '12 at 09:50

Oracle: find duplicate rows in select query

3 Answers3

Linked