Select duplicate values

Opposite of DISTINCT command

[s9901470]

Hi

I know how to select unique entries from a MySQL table using the DISTINCT command, but what is its opposite? I.E. how can I select all entries with the same value. I'm trying to detect people registering with duplicate email addresses.

Many thanks

[coopster]

If you truly do not want duplicates you might also want to add a UNIQUE keyword to that column. You'll have to modify your application to monitor for INSERT/UPDATE failures upon DUPLICATE attempts if you aren't already doing so.

In order to find them now use a COUNT with a GROUP BY clause HAVING COUNT(*) > 1

[physics]

Righto, to find duplicates of foo:

SELECT foo,COUNT(*) FROM table GROUP BY foo HAVING COUNT(*) > 1