每个订单的最新状态以及何时设置为该值

Question

我试图找到每个订单的最新状态，以及它何时设置为该值。我尝试了下面的查询，但结果是这样的。我只是想让它显示最新的状态——不是所有的。非常感谢！

select order_id, updated_at, order_status
from (select order_id, 
updated_at, 
order_status,
row_number() over(partition by order_id, order_status order by updated_at desc)
as rn
from fishtownanalytics.order_status_history) as x
where rn = 1

Answer 1

只需从分区中删除状态，它就可以工作

select order_id, updated_at, order_status
from (select order_id, 
updated_at, 
order_status,
row_number() over(partition by order_id  order by updated_at desc)
as rn
from fishtownanalytics.order_status_history) as x
where rn = 1