SQL SELECT DISTINCT with multuiple columns

Posted on 2009-12-17
Last Modified: 2012-05-08
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

Question by:jweissdandm
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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
    LVL 6

    Expert Comment

    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
    LVL 2

    Author Comment

    would this work?

    select value1,max(value2) as newColumn
    from table1
    group by value1
    LVL 13

    Expert Comment

    Yes. It would work with GROUP BY and AGGREGATE function as well.
    LVL 10

    Accepted Solution

    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%

    LVL 2

    Author Closing Comment

    thanks for the explanation homie

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now