identifying duplicate records...

I want to identify every record where a particular field matches the value in the same field in another record (in the same table).

Example:

Say I have the following table:

id | registrationName | registrationEmail
------------------------------------------------------------
123 | Michael | myoung@lehmans.com
456 | Michael2 | someOtherEmail@bogus.com
789 | Michael3 | myoung@lehmans.com

I want a query to identify the 1st and 3rd record, because those records have identical email addresses. Ideally, it would be really cool if I could select only the 3rd record because it has the higher id number.

In reality, I am dealing with a database that has over 200,000 records, so I obviously don't know each email address before hand.

Thanks in advance to anybody who can help me with this!

Sincerely,
Michael

I've recently done something almost identical to this and found the following MSDN article extremely helpful:

http://msdn.microsoft.com/library/de...ml/ima0056.asp

Hope this helps!

Michael, do I have an exact answer for your or what. Following is the stored procedure I wrote, does exact same thing. It looks for a duplicate email address in the same database and deletes it. If you provide the parmeter 'List', it will also show you the records it found duplicated that it has deleted.

/* Bikram Mann, 10/15/2002
Deletes duplicate email address.
It uses a 'correlated subquery to query same table and match records.
*/

CREATE PROCEDURE up_DELETE_DuplicateEmails (@ShowList char(4) = NULL) AS

IF NOT @ShowList IS NULL
IF @ShowList <> 'List'
BEGIN
PRINT 'Syntax: EXECUTE up_DELETE_DuplicateEmails List'
RETURN
END
ELSE

BEGIN
SELECT Email_Address_ID AS ID,
Email_address AS Email,
Send,
State,
Sent AS Last_Sent,
Last_Update
FROM email_address
WHERE EXISTS
(SELECT email_address FROM email_address Email_Inner
WHERE Email_Inner.email_address = email_address.email_address
AND Email_Inner.email_address_ID < email_address.email_address_ID)
END

DELETE FROM email_address
WHERE EXISTS
(SELECT email_address FROM email_address Email_Inner
WHERE Email_Inner.email_address = email_address.email_address
AND Email_Inner.email_address_ID < email_address.email_address_ID)
GO