Re: cunning SQL query required

Hope I understood this correctly.
If use an inline view to get the "current" view of the table, that probably should help

thats
select <whatever>
from
(select oid,status from table1 A
where time = (select max(time) from Table1 B where A.oid=B.oid))) where condition.

Will this work?

muppet 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.
Received on Tue Mar 06 2001 - 12:24:56 CST