How to Find all the Duplicate Rows in a Table

sounds simple? not to me.

[Anyango]

Hey it should be simple but i cant find out a solution.

I have a table of "users" now lets say i want to show all the rows that have duplicate email addresses, for example if there are mor then one rows having some email address both of them should be showin the the SQL output so i can check their userids. i dont want something like count and stuff, just want both rows to be shown in the output, for all email addresses that occur more then once in the rows of the table.

[adamas]

I think something like this should do the trick. I don't have access to a suitable system at the moment so both logic and syntax are untested.

SELECT t1.keyfield, t1.email, t2.keyfield, t2.email
FROM table AS t1 INNER JOIN table AS t2 ON t1.email=t2.email
WHERE t1.keyfield<>t2.keyfield

The join joins the table to itself and produces every combination of rows where the emails match. The where clause then removes the rows that have matched themselves.

[boxfan]

Or you can try

SELECT COUNT(*) as dups, email_address,userds FROM table GROUP BY email_address, userid HAVING dups > 1;

Untested.

[coopster]

Select duplicate values [webmasterworld.com]