Find Duplicate Records and the Number of Duplicates in SQL (MySQL)
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
(5.00 out of 5)- Tweet
Comments
Brilliant! Thank you very much. I have been looking for this for over an hour now. Thanks again.
Thanks for the tip. But what if I want to delete one of the duplicates? How do I go about this? Thanks!
Amazing. Thank you so much for sharing
Great tip! Thank you for posting it, very very helpful.
thanks man ! its very helpfull ! thx