How to find the latest row and check against parent row?

[Chadi]

Hi

I have two table: header and history. The header record contains Primary Key, PNum and a Status column that can be either 'Open', 'In Progress' or 'Closed'. The header record can only move statuses in that order, ie. from 'Open' to 'In Progress' to finally 'Closed'.

In the History table, there is a foreign key PNum from the header table. Each header row can have one or many (up to 3) history rows.

For example, if my header row has been updated with status twice from 'Open' (first update) to 'In Progress' (second update), I will have two records in the history table (PNum1, Open and PNum1, In Progress).

I would like to find the latest (or the last) history row for each header row and compare the statuses. How would I achieve this using SQL on Oracle 8i?

(I hope I have clearly explained what I want. If not then please let me know and I will provide more clarification.)

Thanks

Chadi

 

[sem]

If your history table doesn't contain some kind of timestamp field you CAN NOT find out exactly the order the rows were inserted. Don't beleive if somebody sugests you to order by rowid or smth else

 

[Chadi]

Sorry. Forgot to mention that both tables contain a timestamp columns.

 

[Bveoracle]

Use a sequence id (fill with an Oracle sequence) for the primary key of the history table (you may add this as a normal column, but it's an excellent PK). Timestamps are not reliable because it's precision goes up to seconds only. It is very imaginary (although it depends on the type of application or proces, so the timestamp may do) that two or more rows of the same date exist, even more if there are many rows.

Order by this sequence and you're certain about the order of update.

Gr. Bart.