Re: help with query to get last row

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

Given your limited data set this works, however larger sets, generated by multi-user access, may return extraneous results as some order_nos may have a status_date which matches the max(status_date) of another order_no:

SQL> select order_no, status, status_date   2 from t_orders
  3 where status_date in
  4 (select max(status_date) from t_orders group by order_no);

  ORDER_NO STATUS STATUS_DA

---------- ---------- ---------
      1223 Closed     01-MAR-05
      1345 Open       01-MAR-05
      2138 Open       02-MAR-05
      1345 InWork     02-MAR-05
      2138 InWork     01-SEP-05

As you can see my 'solution' isn't really much of a solution once you get beyond your limited set of data (and I apologise for not stating this in my prior post). To effect a more robust solution to your problem a slight modification to the query is in order:

SQL> select order_no, status, status_date   2 from t_orders
  3 where (order_no, status_date) in
  4 (select order_no, max(status_date) from t_orders group by order_no);

  ORDER_NO STATUS STATUS_DA

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

Notice order_no is listed now in the IN list and the subquery also returns order_no; this 'ties' the max(status_date) to an order_no.

I would use the latter query to return the values you want as it works no matter how the dates appear in your table.

David Fitzjarrell Received on Thu Jan 26 2006 - 10:54:37 CST