Oracle db get the last row of every sequence

Question

I have a table order_status

id	order_no	seq_no	status
1	123	1	order received
2	123	2	order processing
3	456	1	order received
4	789	1	order received
5	789	2	order processing
6	789	3	order completed

I want to get the status of the max seq_no of each order_no.

That is:

id	order_no	seq_no	status
2	123	2	order processing
3	456	1	order received
6	789	3	order completed

I have tried:

select * from order_status where id IN 
(select id from order_status where max(seq_no) group by order_no)

But oracle db does not recognize the statement. There is an error at group by.

Please help. Thanks.

According to your sample data, the result should be id 2 for row 1 in your results. — Koen Lostrie, Sep 08 '21 at 08:33

Koen Lostrie · Answer 1 · 2021-09-08T08:36:34.877

Use a window function to determine the row_number of each row per order_no, then select only the highest one (rn = 1). I assume that your seq_no values are unique per order so RANK could also be used.

WITH sampledata(id,order_no,seq_no,status)
AS
(
SELECT 1,123,1,'order received' FROM DUAL UNION ALL
SELECT 2,123,2,'order processing' FROM DUAL UNION ALL
SELECT 3,456,1,'order received' FROM DUAL UNION ALL
SELECT 4,789,1,'order received' FROM DUAL UNION ALL
SELECT 5,789,2,'order processing' FROM DUAL UNION ALL
SELECT 6,789,3,'order completed' FROM DUAL
), sampledata_rn
AS
(
SELECT id,order_no,seq_no,status, 
       ROW_NUMBER() OVER (partition by order_no ORDER BY seq_no DESC) AS rn
  FROM sampledata
)
SELECT id,order_no,seq_no,status FROM sampledata_rn
WHERE rn = 1;

        ID   ORDER_NO     SEQ_NO STATUS          
---------- ---------- ---------- ----------------
         2        123          2 order processing
         3        456          1 order received  
         6        789          3 order completed

Oracle db get the last row of every sequence

1 Answers1