Single Distinct Column ? 1

[adamroof]

I have a table variable that im inserting data from 4 tables into. [Full Name], , and [Locale]

The user could have done some fun things became a record in any one of the four tables, however maybe didnt spell thier name the same, or John shares an email with Jane, and both of them were having fun.

Whats the best way to query to just get one distinct email address? It doesnt matter which full name is used, and John and Jane have the same Locale, but i dont want to email John and Jane twice.

 

[tlbroadbent]

Is this what you need?

SELECT DISTINCT
FROM @tblname

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

 

[gmmastros]

If email and locale are the same, then I suggest you use a GROUP BY instead of DISTINCT.

Ex:

Select Min([Full Name]) As FullName,
       [Email],
       [Locale]
From   YourTableName
Group By [Email], [Locale]

If there is only 1 email for someone, they will be returned. If there are duplicates, it will return the one that is alphabetically less than the others.

I should also mention that there could still be duplicate emails returned if there are multiple rows with the same email but different locale's.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[adamroof]

ahh, thanks George

I didnt know you could do a MIN on a varchar column.

Hopefully John and Jane speak the same language!
(that should be checked on tho, ur right, because who knows, maybe John picked up the Rosetta Stone program)