Re: multi-column aggregates

Hello Bruno,

Yes, we have used the distinct on operator in the past and that works quite
well when you have a single ordering column or multiples which don't
contradict each other. The joins would work, but I was hoping for a simpler
solution as this is sql generated from a general purpose query tool. Anyway,
thanks for the suggestion.

Thanks,

-Chris

On Thursday 09 March 2006 05:28 pm, Bruno Wolff III wrote:
> 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).
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Chris Kratz