Re: help with query to get last row

Adam Sandler wrote:
> 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!

Certainly, as it's a basic group by query:

select order_no, status, status_date
from t_orders
where status_date in
(select max(status_date) from t_orders group by order_no)

  ORDER_NO STATUS STATUS_DA

---------- ---------- ---------
      1223 Closed     01-MAR-05
      2138 InWork     01-SEP-05

David Fitzjarrell Received on Wed Jan 25 2006 - 10:25:54 CST