SQL Query to find duplicate values

by | Apr 23, 2015 | SQL

An easy way to find duplicate entries in a table

SELECT DAY_PHONE, COUNT(DAY_PHONE)
FROM CUST
GROUP BY DAY_PHONE HAVING (COUNT(DAY_PHONE)>1)
ORDER BY DAY_PHONE

The above will list the phone numbers that are at least twice in the table and also show the number of occurrences

But often we need to show all the records containing the duplicate entries

we can do that with the following query

;with DUPES as
(
select CUST.DAY_PHONE as PHONE
from CUST
where
.. additional selection here ..
GROUP by CUST.DAY_PHONE
HAVING COUNT(*) > 1
)
SELECT C.CODE, C.NAME, C.DAY_PHONE
FROM DUPES
INNER JOIN CUST as C
ON DUPES.PHONE = C.DAY_PHONE
ORDER BY DUPES.PHONE

the query above will list all records with phone numbers that exist at least twice in the table ordered by phone number