DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=3ovb/9ue5JIFsijVhJWhGKOefHC37yrNL4/jQHySBVhoDPftSeGwXTxf3d+gbwx7fte4Y8WPPNSmzxxpEQW0oNAOxC3UE4Lby/AvZshqjTyHqErP0ZeDjHWoqz3Rv68huQ/oNqm/szf3D/KJb91Zz60xx2rQBbUYxaONWpZhtcY=;
Date: Tue, 3 Jul 2007 11:49:34 -0700 (PDT)
From: chester c young <chestercyoung@yahoo.com>
Subject: Re: Iterate and write a previous row to a temp table?
To: Bob Singleton <bsingleton@ibss.net>
Cc: sql pgsql <pgsql-sql@postgresql.org>
In-Reply-To: <468A98D3.7030206@ibss.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <25291.42349.qm@web54305.mail.re2.yahoo.com>

--- Bob Singleton <bsingleton@ibss.net> wrote:

> Revisiting a Time In Status query I received help on - I'm trying to 
> narrow down a subset of data I return for analysis.
> 
> Given a statusLog as entityId, statusId, timestamp that might look 
> something like
> 
> entityId | statusId | timestamp
> --------------------------------------------
> 001      | HLD      | 2007-06-14 11:07:35.93
> 001      | RDY      | 2007-06-15 11:07:35.93
> 001      | USE      | 2007-06-16 11:07:35.93
> 001      | RDY      | 2007-06-17 11:07:35.93
> 001      | MNT      | 2007-06-18 11:07:35.93
> 
> I need to pull for a given span of time - say 2007-06-16 00:00:00.01 
> (let me call it startTime)  to 2007-06-17 23:59:59.99 (call it
> endTime) 
> in such a way that rows with a timestamp between startTime and
> endTime 
> AND the latest record prior to or equal to startTime are returned. In
> 
> the above simplified example, only the second and third rows would be
> 
> returned.
> 
> A colleague suggested a temp table, but I'm unsure how to iterate
> until 
> I pass the startTime and then write the _previous_ and all subsequent
> 
> rows to a temp table, stopping when I pass the endTime parameter.
> 
> Any hints?
> 
> Thanks!
> Bob Singleton
> 

couldn't you use the simple query:

select * from sometable
where timestamp between
  (select max(timestamp) from sometable where timestamp <= minTime)
  and maxTime


____________________________________________________________________________________
Finding fabulous fares is fun.  
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains.
http://farechase.yahoo.com/promo-generic-14795097