Posted by & filed under 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
(3.67 out of 5)

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

  1. Stuart

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

    Reply
  2. benhur

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>