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

On 12/31/2012 8:33 AM, Robert James wrote:
> I have a query
>
>    SELECT grouping_field, MIN(field_a), MIN(field_b)
>    FROM ...
>    GROUP BY grouping_field
>
> But, instead of picking the MIN field_a and MIN field_b, I'd like to
> pick field_a and field_b from the first record, according to an order
> I'll specify.  In pseudo-SQL, it would be something like this:
>
>    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


http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-DISTINCT

--
Jack Christensen
http://jackchristensen.com/