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