Select DISTINCT problem
-
I need to select an email address and contact name column from a table.
The contact names are all different, but in many cases the email addresses are the same.
I need to be able to select only one of the email addresses, so when i do:
select distinct email_Address from table - then it works fine, but i also need to select the contact name and this is when i get all records obviously.
How would i go about getting only one of each unqiue email address?
Many thanks in advance...
-
What happen if the contact name would be same?
It is the data entry problem, you know that same mail address on any mail server is not allowed. In that senerio your data is wrong.
Any how can you tell me how you would identify which is the correct CONTACT for a specific mail address? otherwise the result is OK
cheers
-
Yes, if the contact name is the same then the distinct does work.
The reason the database is like this at the moment is where we have multiple contacts for any company but dont have the contact's specific email address, so we input the head office email address temporarily, eg info@somecompany.com.
Obviously, when sending the emails we dont want 20 or 30 of the same email going to the info@somecompany.com
With regard to the correct contact for the email - at this stage we are not too concerned with that - as long as at least one email is sent to the company for the time being until we get the contact database more accurate.
Cheers
-
Hi,
Then you can make the query like this
SELECT DISTINCT Email , (
select TOP 1 ContactName from Suppliers S
WHERE Suppliers.EMAIL = S.EMAIL
GROUP BY ContactName,EMAIL
having DATALENGTH(EMAIL)=MIN(DATALENGTH(EMAIL))
 
FROM Suppliers
Let me know if you have some question about this.
cheers
-
That worked thanks a lot.
It even works properly without the "having" clause.
Thanks again....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply