Re: Unique - first

Thomas Kellerer <spam_eater(at)gmx(dot)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