Re: Am I crazy or is this SQL not possible

Is this SQL-99 compliant or a PostgreSQL specific query? I really like it
and have never seen this before.

-Aaron

On 6/1/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Collin Peters" <cadiolis(at)gmail(dot)com> writes:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast. I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
>
> You missed the key idea about how to use DISTINCT ON.
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY email_broadcast_id, date_sent DESC
>
> You order by the DISTINCT ON fields, then one or more additional fields
> to select the representative row you want within each DISTINCT ON group.