Date: Fri, 25 Aug 2006 16:21:45 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: Henry Ortega <juandelacruz@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Best way to do this query..
Message-ID: <20060825202145.GH26439@phlogiston.dyndns.org>
References: <2bffcc330608251316g6ed00710u6e43b243800c6087@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <2bffcc330608251316g6ed00710u6e43b243800c6087@mail.gmail.com>
User-Agent: Mutt/1.5.9i

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