Showing all duplicate records using MySQL

2007-07-22T04:40:00.001-07:00

The following SQL demonstrates how to find all duplicates in a table and show all of the duplicated rows. Some examples of when this could be useful:

Identifying records in a users database where people have signed up with the same email address multiple times
Finding duplicate MP3s in a music database
Allowing the user to view duplicate values before selecting which ones to delete

SELECT *
FROM tblFilms
WHERE 1<(
SELECT COUNT(NonPKID) as CNT
FROM tblFilms as Y
WHERE Y.NonPKID=tblFilms.NonPKID
GROUP by NonPKID
HAVING CNT>1
)

Tomdee