Iterate and write a previous row to a temp table?

Поиск
Список
Период
Сортировка
От Bob Singleton
Тема Iterate and write a previous row to a temp table?
Дата
Msg-id 468A98D3.7030206@ibss.net
обсуждение исходный текст
Ответы Re: Iterate and write a previous row to a temp table?  (chester c young <chestercyoung@yahoo.com>)
Re: Iterate and write a previous row to a temp table?  (Richard Huxton <dev@archonet.com>)
Список pgsql-sql
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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Michael Glaesemann
Дата:
Сообщение: Re: Using escape strings in an insert statement.
Следующее
От: chester c young
Дата:
Сообщение: Re: Iterate and write a previous row to a temp table?