help with query to get last row

Hello...

I have a question about querying the last row of a table... actually I know how to get the last row of a table but what if there's repeating data inside of it (linked to a foreign key) and I want to get the last entry for all distinct entries in the FK column.

Consider the following table:

T_Orders
Order_No, Number(5), FK (references the customer table) Status, VARCHAR2(10)
Status_Date Date

Order_No     Status    Status_Date
1223         Open      01-jan-2004
1223         InWork    01-mar-2004
1223         Closed    01-mar-2005
2138         Open      02-mar-2005
2138         InWork    01-sep-2005

If I do something like:

SELECT * FROM
  T_Orders
WHERE
  Status_Date = (SELECT MAX(Status_Date) FROM T_Orders

that gives me the row:

2138 InWork 01-Sep_2005

Which is great... but I want to know the current status for ALL the unique orders not just the last one in the table... I want the rows returned to look like:

1223 Closed 01-mar-2005
2138 InWork 01-Sep_2005

I'm having trouble wrapping my mind around how to get the last record for a given order number. If there's 50 rows in the table, but only 10 distinct orders, then I want to get 10 records back which give the latest status. Any ideas?

Thanks! Received on Tue Jan 24 2006 - 17:15:44 CST