> 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 | ...
oh, just fyi row-wise comparison enhancment is part of postgresql 8.2.
well, hm. maybe not. all row-wise comparisons do is allow you an easy
way to compare groups of fields instead of one field at a time. Also,
if there is an available index on those fields, it gets an opportunity
to be used to invoke the comparison.
> Would you use a subselect for each rowwise comparison and use the result as
> the value for the column?
this might be possible, and would be trivial to express in a function,
but could be problematic for performance as the subquery has to get
evaluated for every instance of the parent record..in other words the
query will scale poorly with the size of the result set. row-wise
comparison is just syntax sure and a small performance enhancement...
create or replace function last_contact_date(in_person bigint) returns date as
$$
select contact_date from contact_occurance where (person,
contact_date) < ($1, '01/01/9999'::date) order by person desc,
contact_date desc limit 1;
$$ language sql;
the above query will use an index on person, contact_date if it
exists. Your existing solution might be better though: I need to read
through it some more and understand it!
merlin