I’ve been asked the question “How can I return duplicate rows only from a MySQL db table” so many times already, that I’ve decided to post it here in a short article.
It is not something intuitive or readily available (at least it seems), but the solution is short and very simple.
While this query:
SELECT DISTINCT column1
FROM table1
gives us all records without the duplicates, this one returns only the duplicate ones:
SELECT DISTINCT column1
FROM table1
GROUP BY column1
HAVING COUNT(column1) > 1
And by increasing the having count, you can retrieve records with multiple occurrences.