Re: using distinct in a single column to get multiple rows
From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Wed, 21 Sep 2011 06:38:20 +0200
Message-ID: <j5bpnt$7q6$1_at_dont-email.me>
On 2011-09-20 22:28, test_at_m.com wrote: [...]
> If I use - select unique userid, title, zipcode
> it will return all 3 rows because each row is unique
>
> userid title zipcode
> -----------------------------
> 1 mgr 12345
> 2 dev 56789
> 1 mgr 56789
>
> I only want unique userids, so it should return. I realize that there
> are two different rows for userid = 1 but returning only one would be
> OK. The last one would be good.
>
> userid title zipcode
> -----------------------------
> 1 mgr 56789
> 2 dev 56789
>
Date: Wed, 21 Sep 2011 06:38:20 +0200
Message-ID: <j5bpnt$7q6$1_at_dont-email.me>
On 2011-09-20 22:28, test_at_m.com wrote: [...]
> If I use - select unique userid, title, zipcode
> it will return all 3 rows because each row is unique
>
> userid title zipcode
> -----------------------------
> 1 mgr 12345
> 2 dev 56789
> 1 mgr 56789
>
> I only want unique userids, so it should return. I realize that there
> are two different rows for userid = 1 but returning only one would be
> OK. The last one would be good.
>
> userid title zipcode
> -----------------------------
> 1 mgr 56789
> 2 dev 56789
>
select userid, title, zipcode
from (
select userid, title, zipcode,
row_number() over (partition by userid) as rn
from myTable
) as T
where rn = 1
if you have a preference for which row that should be returned, you can add an order by clause:
row_number() over (partition by userid
order by zipcode) as rn
Untested
/Lennart Received on Tue Sep 20 2011 - 23:38:20 CDT