Oracle SQL

stummj
stummj used Ask the Experts™
on
Hello experts

I have a table of arounr 50000 rows. I need to find the rows where one of the columns has duplicate values.

So, for example I may have:

ACCOUNT_NUMBER     REFERENCE     START    END
----------------------------------------------------------------
1234                               REF001           1/1/11     3/2/11
1234                               REF001            4/2/11
1323                               REF002           12/1/11
1263                               REF003            17/4/11
7626                               REF004            16/2/11  17/2/11
6766                               REF004             18/2/11


So in this example I want 4 rows returning. Those with REF001 and REF004, as they both have duplicate references.

Can anyone help me out with the query?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select * from your_table where reference in (select reference,count(1) from your_table group by reference having count(1)>1)
Commented:

SELECT *
FROM yourTable Y
WHERE EXISTS (SELECT reference
                            FROM yourTable T
                            WHERE T.refence = Y.reference
                            GROUP BY reference
                            HAVING COUNT(*) > 1 )
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
select *
from YourTable t
join (select reference from YourTable group by reference having count(*) > 1) ref
on t.reference = ref.reference

Open in new window

The IN method shown first is suitable if there are not much duplicates. Else EXISTS or my approach perform better (if we talk about hundreds or more duplicates).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial