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
>

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

Original text of this message