OT: SQL Question ..., what's the easiest way to ... Options

[SirAndy]

SQL Question:

what's the easiest way to find duplicates in a table?

let's say i have a table (tbl_Users) with 20000 rows and the table has a column for the users email addy (sEmail) and i want to list all the accounts that have duplicate email addys.

i could run a script that takes each row and runs a count(*) on that addy, but that'll take forever.
anything simpler than that ???

(IMG:http://www.914world.com/bbs2/html/emoticons/idea.gif) Andy

[john rogers]

Do it like this:

sqlplus> delete from userID.my_email_data a where rowid <
(select max(rowid)
from userID.my_email_data b
where b.address = a.address
and b.username = a.username);


Notes: userID is the schema owner, my_email_data is the name of the table and address, username are the fields to check for dupes to. You can not use any functions such as substr(....) or you'll get inconsistant results. Additionally, the email field entries have to be exacly alike, even to case or it will fail cause then they aren't really duplicates!