Re: Unique - first - Mailing list pgsql-general

From Tom Lane
Subject Re: Unique - first
Date
Msg-id 13307.1382883980@sss.pgh.pa.us
Whole thread Raw
In response to Re: Unique - first  (Thomas Kellerer <spam_eater@gmx.net>)
List pgsql-general
Thomas Kellerer <spam_eater@gmx.net> writes:
> Robert James wrote on 27.10.2013 14:04:
>> I have a table (x,y,z) - I'd like to take the rows with unique x
>> values - but, when more than one row have the same x value, I want the
>> one with the minimal z value.
>>
>> How can I do that? I can imagine doing it with window functions, but
>> also that regular SQL should be able to do it too.

> Window functions *are* "regular" SQL ;)

Indeed.  The only other easy way I know of involves SELECT DISTINCT ON
(see the "weather reports" example in the SELECT reference page); but
that is most definitely not standard SQL, it is a Postgres-ism.

> A solution without window functions could be something like:

> select t1.x, t1.y, t1.z
> from table t1
>    join (select t2.x, min(t2.y) as min_y
>          from the_table t2
>          group by t2.x
>    ) mt on mt.x = t1.x and mt.min_y = t1.y;

Note that this doesn't work unless x and y form a primary key, else you
get multiple join rows (or no join rows, if one is NULL).  In any case,
it's unlikely to be fast.

I think I've seen some even more esoteric solutions that use only
SQL-92-era features, but lack of caffeine prevents me from recalling them.
In any case, there's a good reason why we invented SELECT DISTINCT ON:
this is just not easy to do in minimal SQL.

            regards, tom lane


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Segmentation fault: pg_upgrade 9.1 to 9.3: pg_dump: row number 0 is out of range 0..-1
Next
From: Rowan Collins
Date:
Subject: Re: Unique - first