SQL SELECT DISTINCT with multuiple columns

I need to grab distinct email address and corresponding fName,lname from my table

I need something like this...

SELECT DISTINCT value1, value2 FROM table

Thanks
LVL 2
jweissdandmAsked:
Who is Participating?
 
lofCommented:
It would in simple cases like here, when you don't specify which related name you want to show.  (probably assuming all are exactly the same)

But let's say you have a column in the same table, where you store information when the data was captured and you want to see only the most recent name for the email address. Than you would have to use row_number() function

the answer you suggested yourself, the simpler one will also get your results quicker so if you don't need any ordering, go for it.

on the other hand example with sub-queries proposed by oferam is the slowest one

According to estimated query plan if you ran all three one after another query cost related to the batch of three would be:

group and max: 16%
row_number:    35%
subquery:         49%

Regards
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select emailaddress, fname, lname
from
(select emailaddress, fname, lname, rn = row_number() over( partition by emailaddress order by fname,lname )  from yourtable  ) a where a.rn = 1
0
 
oferamCommented:
SELECT      DISTINCT emailaddress,
            (SELECT     TOP (1) fname
                  FROM          table
            WHERE      (emailaddress= T.emailaddress)) AS FName,
            (SELECT     TOP (1) lname
            FROM          table
                  WHERE      (emailaddress= T.emailaddress)) AS LName
FROM         table AS T
GROUP BY emailaddress
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
jweissdandmAuthor Commented:
would this work?

select value1,max(value2) as newColumn
from table1
group by value1
0
 
sameer2010Commented:
Yes. It would work with GROUP BY and AGGREGATE function as well.
0
 
jweissdandmAuthor Commented:
thanks for the explanation homie
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.