sql query to display duplicates

Forum Moderators: open

Message Too Old, No Replies

sql query to display duplicates

more parameters needed?

David_M

2:45 pm on Apr 30, 2007 (gmt 0)

This query will list the emails that have duplicates, but what i need to see is the full entries of the duplicates so i can manually delete the ones i dont need.

SELECT email, count( email )
FROM `db_users`
GROUP BY email
HAVING count( * ) >1

Any help appreciated!

[edited by: David_M at 2:46 pm (utc) on April 30, 2007]

David_M

4:57 am on May 1, 2007 (gmt 0)

this seems to do it- a bit slow and cpu intensive on my large lists (10,000+)

SELECT email, list
FROM db_users
WHERE email
IN (

SELECT email
FROM db_users AS Tmp
GROUP BY email
HAVING Count( * ) >1
)
ORDER BY list
LIMIT 0 , 30