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(at)crankycanuck(dot)ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
--Scott Morris