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.