How to select records with the maximum field value for a grouped parameter?

Question

SQL

Two tables:

orders (заказы)
id            
s (статусы заказов)
id | time | status | order_id

The order is considered in the status that was added last (the time field is the maximum for the given order_id).
I cannot make a request that would receive a status at the input, and at the output orders (orders) with this status.

select max(os.time), os.orders 
  from order_statuses os 
    join orders o on os.orders = o.id 
  where os.status = 'NEW' 
  group by os.orders;

only mine returns the last time when the order had the status 'NEW', but I need orders that have 'NEW' - the last status.

Answer 1

SELECT order_id
  FROM order_statuses o
  WHERE (status = 'new')
    AND time = ( SELECT MAX(time) 
                     FROM order_statuses m 
                     WHERE (m.order_id = o.order_id))