Find Duplicate Records and the Number of Duplicates in SQL (MySQL)
Posted By Chris in 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
Related posts:
- Delete Spam Comments in Wordpress via MySQL
- vBulletin / Photopost and MySQL 5
- Unable to load dynamic library php_mysql.dll (MySQL 5.0.41)
- MySQL has a sense of Humor (old)
- MySQL Performance Tip #1: Query Cache
| 
Print This Post
|