Re: cunning SQL query required

On Tue, 6 Mar 2001 17:47:39 -0000, "muppet" <arc_at_hatecoffee.freeserve.co.uk> wrote:

>If there is a more apt newsgroup for this question please let me know, but
>there seems to be a fair number of SQL gurus here who may be able to help
>me.
>
>The basis of my query is a "state" table which is required to maintain an
>audit history as an object goes through it's life. The structure of the
>table is (OrderID, timestamp, state) with OrderID and timestamp together
>being a composite primary key.
>
>Hence every time an OrderID changes it's state, a new record is added to the
>table, with the same ID, current timestamp and the new state.
>
>a short example table
>---------------------
>
>oid time status
>----------------------
>1 1 1
>1 2 2
>1 4 3
>2 5 1
>2 6 2
>2 9 3
>1 23 4
>
>imagine the current time is 25.
>
>The query I'd like to run is
>"get all the OrderIDs which are currently in state 3."
>
>From inspection the answer should be just "2" because although oid 1 was in
>state 3 at time 9, it is now in state 4.
>
>An associated query is "get the current state for each OrderID", which is
>probably easier (but still causing my brain to ache).
>
>I could give up and just pull the whole thing into Java or PL/SQL but I'm
>sure there must be a sensible SQL select statement that will do the job.
>
>any help greatly appreciated,
>Thanks.
>
>
>

select *
 from foo
where time =
(select max(time)
 from foo
 where time <= <currenttime>
 and status = 3)

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Mar 06 2001 - 13:35:27 CST