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.
