[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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

1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
select emailaddress, fname, lname
(select emailaddress, fname, lname, rn = row_number() over( partition by emailaddress order by fname,lname )  from yourtable  ) a where a.rn = 1
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
jweissdandmAuthor Commented:
would this work?

select value1,max(value2) as newColumn
from table1
group by value1
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Yes. It would work with GROUP BY and AGGREGATE function as well.
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%

jweissdandmAuthor Commented:
thanks for the explanation homie

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now