Retrieving first less than value in a joined table in MySQL

Question

I have the 3 following tables:

I want to get a table that consist of three columns which are:

order_id

last order status before contact time

status_description

So the desired output is highlighted in red in the order_status table

I managed to get all the order status before contact time but the required is only 1 for each order_id (I added the column Timestamp for illustration):

SELECT o.order_id, o.order_status_id, m.status_description, o.Timestamp
FROM
    contact c
        JOIN
    order_status o ON o.order_id = c.order_id
        JOIN
    meta_status m ON o.order_status_id = m.order_status_id
    WHERE o.Timestamp < c.Contact_time 
ORDER BY o.order_id;

Answer 1

You can use row_number()

select * from
(
SELECT o.order_id, o.order_status_id, m.status_description, o.Timestamp,row_number() over(partition by o.order_id order by o.Timestamp desc) as rn
FROM
    contact c
        JOIN
    order_status o ON o.order_id = c.order_id
       left JOIN
    meta_status m ON o.order_status_id = m.order_status_id
    where o.Timestamp < c.Contact_time 
)A where rn=1