On 27/10/2013 13:04, Robert James wrote:
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.
My personal favourite approach, assuming you can rely on Postgres 9.0 or
higher, is to define a first() aggregate as shown here:
http://wiki.postgresql.org/wiki/First/last_%28aggregate%29
Once created, this can be used with the order_by_clause of the aggregate
expression as shown here:
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
(That's why it requires 9.0, since earlier versions had no such clause).
So in your case, you could run
SELECT
x,
first(y order by z) as y_with_lowest_z
FROM
xyzzy
GROUP BY
x
I find this a lot easier to understand than window functions; I've no
idea how its performance compares.
--
Rowan Collins
[IMSoP]
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
