Showing all duplicate records using MySQL

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
)