Re: multi-column aggregates

On Thursday 09 March 2006 02:18 pm, Merlin Moncure wrote:
> Chris Kratz wrote:
> > Well for anyone else who may be interested in doing something similar,
> > here is what we did. It does require typecasting going into the
> > functions, composite types and using the dot notation to get the value
> > back out of the composite object returned. But it works.
> >
> > This is what we wanted...
> >
> > select last(cur_date, some_column) from some_table....
> >
> > We got this close...
> >
> > select (last((cur_date, some_column)::last_int_agg)).value as last_int
> > from...
>
> have you looked at new row-wise comparison feature (i might be
> misunderstanding your problem)?
>
> select some_column from some_table where (cur_date, some_column) <
> '01/01/06', 99999999) order by cur_date desc, some_column desc limit
> 1;
>
> this will give you the highest value of some_column on the abitrarily
> chosen date 01/01/06 (assuming all values of some_column are less than
> 99999999).
>
> Merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Hello Merlin,

I have to confess I'm not real familiar with rowwise comparisons. Would this
work when you have a large number of rows. For example, give me all
individuals and their income their favorite TV Show the first and last times
they were contacted. ie | Person | First Favorite | Last Favorite | ...

Would you use a subselect for each rowwise comparison and use the result as
the value for the column?

Thanks,

-Chris