Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 9A5759FBD10
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  6 Jul 2007 13:15:27 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 67362-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  6 Jul 2007 13:15:11 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from ganymede.ibss.net (66.49.75.50.nw.nuvox.net [66.49.75.50])
	by postgresql.org (Postfix) with ESMTP id CC3729FBCF8
	for <pgsql-sql@postgresql.org>; Fri,  6 Jul 2007 13:15:22 -0300 (ADT)
Received: from [172.23.1.200] (jumper.ibss.net [172.23.1.200])
	by ganymede.ibss.net (8.13.6/8.13.4) with ESMTP id l66GFCou010151
	for <pgsql-sql@postgresql.org>; Fri, 6 Jul 2007 12:15:12 -0400
Message-ID: <468E6D0F.5070701@ibss.net>
Date: Fri, 06 Jul 2007 12:25:51 -0400
From: Bob Singleton <bsingleton@ibss.net>
User-Agent: Thunderbird 1.5.0.10 (X11/20070302)
MIME-Version: 1.0
To: sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: Iterate and write a previous row to a temp table?
References: <25291.42349.qm@web54305.mail.re2.yahoo.com>
In-Reply-To: <25291.42349.qm@web54305.mail.re2.yahoo.com>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: ClamAV version 0.90.3,
	clamav-milter version 0.90.3 on ganymede.ibss.net
X-Virus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/38
X-Sequence-Number: 28773

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
chester c young wrote:
<blockquote cite="mid25291.42349.qm@web54305.mail.re2.yahoo.com"
 type="cite">
  <pre wrap="">--- Bob Singleton <a class="moz-txt-link-rfc2396E" href="mailto:bsingleton@ibss.net">&lt;bsingleton@ibss.net&gt;</a> wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">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

    </pre>
  </blockquote>
  <pre wrap=""><!---->
couldn't you use the simple query:

select * from sometable
where timestamp between
  (select max(timestamp) from sometable where timestamp &lt;= minTime)
  and maxTime


  </pre>
</blockquote>
This works very well unless I have no records where timestamp &lt;=
minTime. If I try&nbsp; <br>
&nbsp;&nbsp;&nbsp; select max(timestamp) from sometable where timestamp &lt;= minTime;<br>
I get<br>
&nbsp;max<br>
-----<br>
<br>
(1 row)<br>
And the blank row/line/value/? confuses the between call - I get 0 rows
returned.<br>
&nbsp;<br>
(if minTime is later than at least one row's timestamp I will see
something like)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; max<br>
------------------------<br>
&nbsp;2007-06-08 17:42:00.18<br>
(1 row)<br>
<br>
<br>
Any help greatly appreciated!<br>
bs<br>
</body>
</html>