(MySql) Finding Duplicates in a column

Many times, we need to figure out if there is a duplicate in a particular column of a database. Here is a quick guide on how to find duplicates in a column in MySql.

MySql - How to find Duplicates in a Column

Here is the easiest way to find duplicate in column using group by and count features of mysql

# assuming a mysql table having column named 'id' and 
# 'value'

# Find all duplicate values and their count
SELECT value, COUNT(*) c FROM table GROUP BY value 
HAVING c > 1;

This will return the duplicate values and their count.

Sometimes we need to find the primary key of rows containing duplicate values. In such case, here is a way to achieve that using group_contact feautre of mysql

# print ids of duplicate value along-with the value and 
# their count

SELECT GROUP_CONCAT(id), value, COUNT(*) c FROM table 
GROUP BY value HAVING c > 1;