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

user_19275912 0
Viewed 69

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 !

mysql sql
2 Answers

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

user_191745700

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.

Exclude orders with same date that were placed and not processed -

select orderid,clientid,max(timestamps) timestamps,order_status from orders o1
where
not exists
(select 1 from orders o2
where o2.orderid = o1.orderid
and o2.timestamps = o1.timestamps
and o2.order_status <> o1.order_status)
and order_status<>'Processed'
group by orderid,clientid,order_status

Fiddle here

user_139153130
Related