select max date in oracle

Question

I have table where it contains order_id, status and update_date. status may be success or failure. Each order can have these status multiple times with different update date except 'success' status like below.

order_id status  update_date
1001    failure 01-01-2015
1001    failure 02-01-2015
1001    success 03-01-2015
1001    failure 04-01-2015
1002    failure 02-01-2015
1002    failure 03-01-2015

I need to select each order id with latest update date with their respective status and in case if the order has success status, then its respective update date like below

1001 success 03-01-2015
1002 failure 03-01-2015

pls help to get this. thanks

score 2 · Accepted Answer · answered Nov 10 '17 at 18:44

Try this:

SELECT order_id,
  status,
  update_date
FROM(SELECT order_id,
            status,
            update_date,
            ROW_NUMBER() over (partition BY order_id order by DECODE(status,'success',1,0) DESC,update_date DESC) rn
      FROM test1)
WHERE rn=1;

P.S : test1 is my table name with your data.

select max date in oracle

1 Answers1