Re: Unique - first

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 ;)

select x,y,z
from (
   select x,y,z,
          min(y) over (partition by x) as min_y
   from the_table
) t
where y = min_y;

Instead of min() you could also use row_number() or dense_rank() to find the minimum value.

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;

But I'm pretty sure the solution with the window function will perform better.