Finding Duplicate Values In A MySQL Table

To find duplicate values you need to use the MySQL COUNT() function and then pick out all of the counts that are greater than one.

SELECT value,COUNT(value) AS Count
FROM test
GROUP BY value
HAVING (COUNT(value) > 1)
ORDER BY Count DESC;

Conversely you can also select the rows that only have a single entry.

SELECT value,COUNT(value) AS Count
FROM test
GROUP BY value
HAVING (COUNT(value) = 1)
ORDER BY Count DESC;

However, it is very nice to pick out the duplicate entries in a table, but you might still need to do something with them. Here is a query to delete any duplicate rows from a table. It does a simple self join and deletes the row value with the lowest ID.

DELETE bad_rows.*
FROM tests AS good_rows
INNER JOIN tests AS bad_rows ON bad_rows.number = good_rows.number
AND bad_rows.id > good_rows.id;

More information on this deletion query and other methods of deleting duplicates can be found at Xaprb.com.

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
4 + 5 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.