Search code examples
sqlwindow-functions

Most recent status for each order and when was it set to that value

I'm attempting to find the most recent status for each order and when it was set to that value. I tried the query below but what happens is this. I just want it show the most recent status not all of them. Thank you so much! 

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
Solution

  • just remove status from partition it will work

    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