edo1h

select id from orders o where
    ( select status from order_statuses os 
          where os.order_id = o.id order by time desc limit 1
    )='NEW'

если в orders записей намного меньше, чем в order_statuses, и производительность важна, то этот вариант будет быстрее того, который предложил Константин Цветков.

по order_statuses нужен индекс по order_id, time.
в mssql вместо limit 1 в конце подзапроса будет top 1 сразу после select.

ещё быстрее завести отдельную таблицу с текущим статусом заказа, но это может стать узким местом при активной записи.