identifying duplicate records...

[mrvy1]

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

[siu99adw]

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!

[Bik]

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