June 12, 2013 at 3:16 pm
Let's say I have this dataset.
UserIDEmail
------------------------------------
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.
June 12, 2013 at 3:33 pm
Kaptnik (6/12/2013)
Let's say I have this dataset.UserIDEmail
------------------------------------
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,
from
(select
rn = row_number() over (partition by UserID order by (select null)),
UserID,
from
yourTableNameHere
) dt
where
rn = 1;
Untested, but it should work.
June 12, 2013 at 3:34 pm
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.
June 12, 2013 at 3:42 pm
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;
June 12, 2013 at 3:53 pm
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!
June 12, 2013 at 3:59 pm
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:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply