Find duplicate records in MySQL

While working with databases, we all get into the situation of finding unique records. We want to eliminate the records that repeat again and again in the resultset. Because this is a frequent requirement, MySQL has provided an easy and straightforward way to do this task. As you must be knowing, finding distinct (unique) records is easy in SQL by using DISTINCT keyword.

Using DISTINCT keyword, you can find unique records. But how about finding only those records that have duplicates?

Although it happens less frequently, but at times, instead of unique records, we may need to find the duplicate records only! Unfortunately, there is no ready-made SQL keyword to find duplicate records. The other day, I needed to find only duplicate records in a table -so I used the following SQL statement as solution. I am sharing it with you:

SELECT COUNT(id) as c FROM table_name GROUP BY id HAVING c > 1;

This statement finds you only those records that have duplicates in a resultset. I hope this will save you time. Please do let me know if you’ve a better solution for achieving the same results.

I have also published a set of useful MySQL commands. You may want to look at these as well.

Please feel free to ask any questions you’ve on this topic. I will be happy to try and help you. Thank you for using TechWelkin!