Find Duplicate Records and the Number of Duplicates in SQL (MySQL)

Posted By Chris Stinson in MySQL on January 18, 2008

I have found the need to account for duplicates and the number of duplicates in many databases. I’ve come across many people who have a minimal understanding of SQL and create wild php scripts to find duplicates, when really all that is needed is a single SQL statement.

select COLUMN, count(COLUMN) as cnt from TABLE group by COLUMN order by cnt desc

Where COLUMN is the column (all the same) you wish to find duplicate entries for. It will display the number of duplicates as an integer in descending order grouped by each entry in the COLUMN column. TABLE is the database table you wish to analyze.

Ex. Find the number of instances of the same name in an employee database:

select FirstName, count(FirstName) as cnt from Employee_Info group by FirstName order by cnt desc

Related posts:

1. Converting MySQL tables from Latin1 to UTF8
2. Delete Spam Comments in WordPress via MySQL
3. vBulletin / Photopost and MySQL 5
4. Unable to load dynamic library php_mysql.dll (MySQL 5.0.41)
5. MySQL has a sense of Humor (old)

(average: 5.00 out of 5)

5 Comments |  Print This Post

5 Responses to “Find Duplicate Records and the Number of Duplicates in SQL (MySQL)”

1. 

   Stuart

   February 10, 2009 at 4:27 am

   Brilliant! Thank you very much. I have been looking for this for over an hour now. Thanks again.

   Reply
2. 

   benhur

   March 12, 2009 at 2:40 am

   Thanks for the tip. But what if I want to delete one of the duplicates? How do I go about this? Thanks!

   Reply
3. 

   Jason

   October 27, 2009 at 12:28 am

   Amazing. Thank you so much for sharing

   Reply
4. 

   Chris

   February 9, 2010 at 10:42 am

   Great tip! Thank you for posting it, very very helpful.

   Reply
5. 

   andrew

   July 29, 2010 at 2:59 am

   thanks man ! its very helpfull ! thx

   Reply