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.kratz@xxxxxxxxxxxxxx> 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
