Re: Unique - first

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]