I've got two tables in an ORA DB. One is called WORKORDER and the other is called WOSTATUS. They have a one to many relationship. For every row in the WORKORDER table, there is at least one row in the WOSTATUS table. The way the application works is that every time that you modify the status field of the work order, the application inserts a row into the WOSTATUS table. The tables have a number of columns but the ones the "connect" the two together are:
WONUM and SITEID.
The query that I'm attempting to write is one that finds WORKORDERs that have a status of 'INPRG' and where the previous status for those WORKORDERs, which you would find in the WOSTATUS table, is 'WMATL'.
I've attached an example to show you data in both tables and the result set I'm looking for.
I need to do this as a subselect with the subselect looking with the select looking for WORKORDERs with a status of 'INPRG' but then with the "subselect" limiting the result set to only those where the 2nd most recent WOSTATUS row is 'WMATL'.
I hope this make's sense.