select addresses with distinct email address

Hi

I need to generate a CSV of names and address from a table and I would like only one result (it doesn't matter which) per distinct email address. If possible, they also need to be ordered by DateAdded descending so I can select just the most recent 1000 results.

My table contains a number of fields such as FirstName, LastName, Address1, Address2, Postcode, Email etc. A plain distinct doesn't work as they may have entered a slightly different name/address combination each time they place an order. So I thought doing it on a distinct email address would be the way.

I have tried populating a temporary table with all distinct email addresses and using a left join but still get multiple results.

For example if the fields were FirstName, LastName, Email and the data looked like

John Williams john@somewhere.com
Jonathan Williams john@somewhere.com
Mary Thomas mary@blah.com

I would want to return just
John Williams john@somewhere.com
Mary Thomas mary@blah.com

as the first two records have the same email address so only one needs to be returned.

I've tried a variety of JOIN, DISTINCT and GROUP BY statements but can't get what I need.

Any help would be gratefully appreciated.

re: select addresses with distinct email address

Group by the email address should work.

re: select addresses with distinct email address

Hi

Thanks for the reply ziycon. I had tried code such as

SELECT ShopperEmail, BillingFirstName, BillingLastName FROM Orders WHERE OrderDate IS NOT NULL GROUP BY ShopperEmail ORDER BY OrderDate DESC

but then I got an error saying "Column 'Orders.BillingFirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

If I add the other fields into the GROUP BY as seems to be required eg.

SELECT TOP 100 ShopperEmail, BillingFirstName, BillingLastName FROM Orders WHERE OrderDate IS NOT NULL GROUP BY ShopperEmail, BillingFirstName, BillingLastName, OrderDate ORDER BY OrderDate DESC

I get duplicates if there is any slight difference in the firstname or last name they have entered (which is what I'm trying to avoid) or if they have made two orders which have different dates.

I hope that clarifies

Thanks

re: select addresses with distinct email address

This should work?

re: select addresses with distinct email address

No sorry, that doesn't work for me. According to the error message I get OrderDate has to be included in GROUP BY:

"Column "TempOrders.OrderDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

Also, your code doesn't distinguish between 2 people who have the same surname and email address but slightly different first names. If you look at my first post, I don't want both John Williams and Jonathan Williams as they have the same email address, but because FirstName is included in the GROUP BY clause they are returned as separate results.

Thanks though

re: select addresses with distinct email address

Ok, try this:

re: select addresses with distinct email address

No sorry!

The DISTINCT command is selecting all those with unique FirstName & LastName & Email combination. So as John is distinct from Jonathan I get all three results returned again.

re: select addresses with distinct email address

Depending on the size of your table, this should do fine:


Happy Coding!!

--- CK

re: select addresses with distinct email address

CK that's brilliant, thank you. It does just what I need.

Now I just need to see if I can get my head around it!

Thanks again.