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

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 !

CodePudding user response：

On MySQL 8 you can use row_number

with cte as
( select *,row_number() over(partition by orderid order by timestamps desc ) as row_num 
  from orders
  where order_status='Placed'
) select orderid,clientid,timestamps,order_status 
  from cte
  where row_num=1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7412ae974e31e3c9a0027e6a9808619d

CodePudding user response：

For version below < 8.0

select * from 
(select orderid,clientid,max(timestamps) timestamps,order_status
from orders
where order_status='Placed'
group by orderid,clientid,order_status) t

DB fiddle here.