Re: Best way to do this query..

On Fri, Aug 25, 2006 at 04:16:07PM -0400, Henry Ortega wrote:
> I have the following:
> 
> name      effective         tstamp                                   rate
> John       01-01-2006     2005-12-07 13:39:07.614945    115.00
> John       01-16-2006     2006-01-07 13:39:07.614945    125.00
> John       01-16-2006     2006-01-09 15:13:04.416935    1885.00
> 
> I want the output to be:
> name      effective         end_date        rate
> John       01-01-2006     01-15-2006    115.00
> John       01-16-2006                         1885.00
> 
> What is the best way to do this? This is on a huge table and what I
> have right now is quite slow. Any ideas?

This is not tested, and it's just a sketch.  Seems like you need
another column, and probably a trigger that makes decisions on insert
time about whether the column is to be updated.  That column would be
an end_date column, default infinity.  When an insert comes along,
you DO ALSO update the old row's end_date with a new column.  Then
you do DISTINCT ON max(effective) and use a CASE statement to
suppress the infinity on the current rate.

Does that help?

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.    --Scott Morris