Duplicate Records MYSQL

Find Duplicate Entries MYSQL

There could come a time when you want type a quick query that will find duplicate values in a column. For example, you could have multiple values for an IP address or you could be checking how many times a user has logged into an account.

The code below can be used to find duplicate email addresses in a table where the column name is email. The query below searches for the records that exists more than once. Simply change the number 1 to a higher number for filtering.

 SELECT * FROM tablename WHERE email IN (SELECT email FROM tablename GROUP BY email HAVING count(email) > 1) ORDER BY email ASC; 

If you just want to make a quick count of the duplicates in a column, you can use the one-liner shown below.

 SELECT COUNT(*), columname FROM tablename GROUP BY columname HAVING COUNT(*) > 1 

August 18, 2014 by kent