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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
