How can I take the last row of every distinct ID that only has the status 'Placed' on mysql

Question

I am trying to find a way to get the last row of every order for which the last status is Placed on Mysql.

The data looks like this:

orderid	clientid	timestamps	order_status
1234	885	2022-01-01 08:00:00	Placed
1234	885	2022-01-01 09:00:00	Placed
1234	885	2022-01-01 12:00:00	Processed
1235	885	2022-01-02 09:00:00	Processed
1235	885	2022-01-01 14:00:00	Placed
1236	886	2022-01-02 08:00:00	Placed
1236	886	2022-01-02 08:00:00	Processed

and here is my query:

SELECT *
FROM orders
WHERE timestamps = (SELECT MAX(timestamps 
                    FROM orders 
                    GROUP BY orderid) and order_status = 'Placed'

But since for a same timestamp it could be both placed and processed like the order id 1236 I want to exclude this row completly since I only want the one that are just placed not processed. I tried to add row numbers but it didn't really helped me. Anyone has an idea or a hint on how to solve that? Thanks in advance !

Answer 1

This can be easily solved using row_number()

• Find the last record per orderid. Give precedence to Processed over Placed (by using descending alphabet sorting).
• Take only last records where order_status is Placed

---

select *

from  (select row_number() over (partition by orderid 
                                 order by timestamps desc, order_status desc) rn
              ,o.*
       from orders o 
       ) o
       
where  rn = 1 and order_status = 'Placed'

rn	orderid	clientid	timestamps	order_status

fiddle