Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting rows with distinct values for a column. Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 3:53 PM
Points: 8, Visits: 21
Let's say I have this dataset.

UserID Email
------------------------------------
1 jamesbond@mi6.com
1 jbond@mi6.com
1 jamesb@mi6.com
2 mpenny@mi6.com
3 q@mi6.com
3 weapons_dept@mi6.com

How do I pick out just one email address for each user id? I'm struggling to write a query to do it.

Just as long as I get a result set with 3 user ids, and 3 email addresses, I'm happy. It doesn't matter which email address is selected.
Post #1462835
Posted Wednesday, June 12, 2013 3:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
Kaptnik (6/12/2013)
Let's say I have this dataset.

UserID Email
------------------------------------
1 jamesbond@mi6.com
1 jbond@mi6.com
1 jamesb@mi6.com
2 mpenny@mi6.com
3 q@mi6.com
3 weapons_dept@mi6.com

How do I pick out just one email address for each user id? I'm struggling to write a query to do it.

Just as long as I get a result set with 3 user ids, and 3 email addresses, I'm happy. It doesn't matter which email address is selected.



This:


select
UserID,
Email
from
(select
rn = row_number() over (partition by UserID order by (select null)),
UserID,
Email
from
yourTableNameHere
) dt
where
rn = 1;


Untested, but it should work.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1462840
Posted Wednesday, June 12, 2013 3:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,499, Visits: 7,549
As easy as

SELECT UserID, 
MAX(Email) Email
FROM MyTable
GROUP BY UserID --Edited post to add this line

If you need a specific email, you need to define the priority.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1462841
Posted Wednesday, June 12, 2013 3:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
Luis Cazares (6/12/2013)
As easy as

SELECT UserID, 
MAX(Email) Email
FROM MyTable

If you need a specific email, you need to define the priority.


Yep, easy except you are missing something:


SELECT
UserID,
MAX(Email) Email
FROM
MyTable
GROUP BY
UserID;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1462846
Posted Wednesday, June 12, 2013 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 3:53 PM
Points: 8, Visits: 21
Yup, I was going to say that it was missing the GROUP BY clause as well.

Both solutions work, but I'm going with Luiz's as it's very simple, and since I don't care which email address I get, I don't need to prioritize.

Thanks guys!
Post #1462849
Posted Wednesday, June 12, 2013 3:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,499, Visits: 7,549
Lynn Pettis (6/12/2013)
Luis Cazares (6/12/2013)
As easy as

SELECT UserID, 
MAX(Email) Email
FROM MyTable

If you need a specific email, you need to define the priority.


Yep, easy except you are missing something:


SELECT
UserID,
MAX(Email) Email
FROM
MyTable
GROUP BY
UserID;




I need to sleep some more



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1462850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse