(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;