Re: using distinct in a single column to get multiple rows

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 21 Sep 2011 19:28:59 +0200
Message-ID: <9dul6sFuaiU1_at_mid.individual.net>

On 09/21/2011 06:38 AM, Lennart Jonsson wrote:
> 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

In that case I'd prefer this as it is so much simpler:

select userid, title, min(zipcode) as zip_min from myTable
group by userid, title

Kind regards

        robert Received on Wed Sep 21 2011 - 12:28:59 CDT