--- Bob Singleton <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
