Re: Picking the first of an order in an aggregate query

Jack Christensen <jack@jackchristensen.com> writes:
> On 12/31/2012 8:33 AM, Robert James wrote:
>> SELECT grouping_field, FIRST(field_a), FIRST(field_b)
>> FROM ...
>> ORDER BY field_c DESC, field_d ASC, myfunc(field_e) ASC
>> GROUP BY grouping_field
>>
>> How can I do that with Postgres?

> select distinct on (grouping_field), field_a, field_b
> from ...
> order by grouping_field, field_a asc, field_b asc

Another possibility, if you're using PG 8.4 or newer, is to use window
functions.  It'd go something like

select grouping_field, first_value(field_a) over (partition by grouping_field order by field_a), ...

if memory serves (I'm not quite sure whether you need the PARTITION BY
bit if there's a global GROUP BY in the query).

The DISTINCT ON syntax is a Postgres-ism, while window functions are
SQL-standard so have at least some chance of being portable, if that
matters to you.

            regards, tom lane