Finding Duplicate Values In A MySQL Table

6th February 2008 - 2 minutes read time

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.

  1. SELECT VALUE,COUNT(VALUE) AS COUNT
  2. FROM test
  3. GROUP BY VALUE
  4. HAVING (COUNT(VALUE) > 1)
  5. ORDER BY COUNT DESC;

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

  1. SELECT VALUE,COUNT(VALUE) AS COUNT
  2. FROM test
  3. GROUP BY VALUE
  4. HAVING (COUNT(VALUE) = 1)
  5. 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.

  1. DELETE bad_rows.*
  2. FROM tests AS good_rows
  3. INNER JOIN tests AS bad_rows ON bad_rows.number = good_rows.number
  4. 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.