By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

select addresses with distinct email address

P: 5
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.
Aug 24 '09 #1
Share this Question
Share on Google+
8 Replies


100+
P: 384
Group by the email address should work.
Aug 24 '09 #2

P: 5
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
Aug 24 '09 #3

100+
P: 384
This should work?
Expand|Select|Wrap|Line Numbers
  1. SELECT ShopperEmail, BillingFirstName, BillingLastName 
  2. FROM Orders 
  3. WHERE OrderDate IS NOT NULL 
  4. GROUP BY ShopperEmail, BillingFirstName, BillingLastName 
  5. ORDER BY OrderDate DESC
  6. LIMIT 0,100
Aug 24 '09 #4

P: 5
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
Aug 24 '09 #5

100+
P: 384
Ok, try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ShopperEmail, BillingFirstName, BillingLastName 
  2. FROM Orders 
  3. WHERE OrderDate IS NOT NULL 
  4. GROUP BY ShopperEmail, BillingFirstName, BillingLastName, OrderDate
  5. ORDER BY OrderDate DESC
  6. LIMIT 0,100
Aug 24 '09 #6

P: 5
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.
Aug 24 '09 #7

ck9663
Expert 2.5K+
P: 2,878
Depending on the size of your table, this should do fine:

Expand|Select|Wrap|Line Numbers
  1. select * from yourtable y1
  2. where y1.yourprimarykey in 
  3. (select yourprimarykey = (select top 1 y2.yourprimarykey from yourtable y2 where y2.email = emails.email)
  4. from
  5. (select distinct Email from yourtable) emails)
  6.  
  7.  

Happy Coding!!

--- CK
Aug 24 '09 #8

P: 5
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.
Aug 25 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.