A client of mine purchased an opt-in b2b marketing list, and wanted to send an email to everyone on the list. Yes, I know, quasi-spam. He's not sending it from any of my servers, so he'll have to find somewhere else to do so. At any rate, he needed the data cleaned up for him, so he sends it over to me. The only two columns really needed are the name and email column, as he wants each email to address the person by name. The problem with this list is that there are no exact duplicates, but there are many many occurences of entries that have the same email address, but a different name in the name column. No one email address should be emailed twice. Here's the SQL script I wrote to clean the data up for him:

DECLARE @T table(TName varchar(29), TEmail varchar(48))

INSERT @T(TName, TEmail)
SELECT M.Name, M.Email
FROM Marketing_REAgents M
WHERE (M.Email IN (SELECT Email FROM Marketing_REAgents GROUP BY Email HAVING (COUNT(Email) > 1)))
AND (M.ID = (SELECT TOP 1 ID FROM Marketing_REAgents WHERE (Email = M.Email)))
ORDER BY M.Email

DELETE
FROM Marketing_REAgents
WHERE (Email IN (SELECT Email FROM Marketing_REAgents
GROUP BY Email
HAVING (COUNT(Email) > 1)))

INSERT Marketing_REAgents(Email, Name) SELECT TEmail, TName FROM @T