where EmailAddress IN
( select EmailAddress
having count(*) > 1 )
I might misunderstand the poster's intention, but I thought he does not want duplicate email addresses. Your query will return those entries where there are duplicate email addresses (and only those)
Yes duplicates are allowed as unfortunately they get imported from a client's own spreadsheet so we have no control over how they initially manage their captures.
But like the man said, we don't want to send duplicate mails to the list. I note whem we omit the SubscriberID from the query we get what we want as far as email addresses go, but we also do need the ID's to go back to the client.
So I hope that helps a bit for all you SQL guru's.
That's a very good question, but it doesn't matter which ID we pick up so long as we get one, so no preference here.
You can probably get the results you need with out using 'Distinct'. Try a GROUP in a subgroup by EmailAddress, as follows:
select EmailAddress, FirstName, Surname, T.SubscriberID
from TestMailingList T
join (select EmailAddress, Max(SubscriberID) as SubscriberID
group by EMailAddress) as M
on M.SubscriberID = T.SubscriberID and T.EmailAddress = M.EmailAddress
I didn't know if your SubscriberID is unique... if it is, then you don't need T.EmailAddress = M.EmailAddress in your join.
Since you don't care which SubscriberID is returned (if multiple have the same e-mail address) this will give you the last SubscriberID for each EmailAddress. You could just as simply used MIN if you wanted the first SubscriberID for each EmailAddress.