From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Picking the first of an order in an aggregate query |
Date: | 2012-12-31 15:37:37 |
Message-ID: | 24986.1356968257@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jack Christensen <jack(at)jackchristensen(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-12-31 16:36:27 | Re: UNION and pg_restore |
Previous Message | Adrian Klaver | 2012-12-31 14:54:59 | Re: rights for schema |