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
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. I'm sure I'm missing something but can't see what!
Any help would be gratefully appreciated.