cunning SQL query required

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. Received on Tue Mar 06 2001 - 11:47:39 CST