SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting rows with distinct values for a column.


Selecting rows with distinct values for a column.

Author
Message
Kaptnik
Kaptnik
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93795 Visits: 38955
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.

Cool
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)
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41468 Visits: 19815
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93795 Visits: 38955
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;




Cool
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)
Kaptnik
Kaptnik
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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!
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41468 Visits: 19815
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;





w00t I need to sleep some more Hehe


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search