From: | chester c young <chestercyoung(at)yahoo(dot)com> |
---|---|
To: | Bob Singleton <bsingleton(at)ibss(dot)net> |
Cc: | sql pgsql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Iterate and write a previous row to a temp table? |
Date: | 2007-07-03 18:49:34 |
Message-ID: | 25291.42349.qm@web54305.mail.re2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
--- Bob Singleton <bsingleton(at)ibss(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2007-07-03 18:57:20 | Re: Iterate and write a previous row to a temp table? |
Previous Message | Bob Singleton | 2007-07-03 18:43:31 | Iterate and write a previous row to a temp table? |