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.

Stefan · Accepted Answer · 2013-01-18 15:07:29Z

9

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

answered Jan 18, 2013 at 15:07

Stefan

1062 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

This also worked with Oracle 12c. I can take a nap now.

Community · Accepted Answer · 2019-01-18 11:28:48Z

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, 2019 at 11:28

CommunityBot

11 silver badge

answered Sep 6, 2014 at 6:35

surendra

611 silver badge1 bronze badge

1 Comment

user613114 Over a year ago

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.

fthiella · Accepted Answer · 2012-12-08 09:23:34Z

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 8, 2012 at 9:23

answered Dec 8, 2012 at 9:18

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

2 Comments

user613114 Over a year ago

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.

fthiella Over a year ago

@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

Collectives™ on Stack Overflow

Oracle: find duplicate rows in select query

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related