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,
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.
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy