Re: help with query to get last row

On 24 Jan 2006 15:15:44 -0800, "Adam Sandler" <corn29_at_excite.com> 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!

select * from t_orders t1
where status_date =

          (select max(status_date) 
           from t_orders t2 
          where t2.order_no = t1.order_no
         ) 

--
Sybrand Bakker, Senior Oracle DBA