Re: multi-column aggregates

On Thu, Mar 09, 2006 at 12:56:21 -0500,
Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> wrote:
> Hello All,
>
> Is there any way in postgres to have an aggregate that uses input from two
> columns without using composite types? The example we are working on is a
> first or last aggregate which requires a data value and a date column. The
> result would be to find the latest date within the group and return the
> associated data value. Since there may be multiple date columns each with
> dependent columns, we can't use a sort by date and a simpler first or last
> aggregate.

It sounds like you could use the (postgres specific) DISTINCT ON construct
to do what you are looking for. Something like:
SELECT DISTINCT ON (groupcol) datacol ORDER BY groupcol DESC, datecol DESC;
If there are multiple versions of this being done at the same time, you
can do them separately and then join then on the group key (groupcol in
the example).