Как выбрать записи с максимальным значением поля для параметра с группировкой?

Question

[piatachki]

Две таблицы:

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

Заказ считается в статусе, который был добавлен последним (поле time максимальное для данного order_id).
Не могу составит запрос, которые на вход получал бы статус, а на выходе заказы (orders) с данным статусом.

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;

только мой возвращает последнее время, когда у заказа был статус 'NEW', а нужно заказы, у которых 'NEW' - последний статус.

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))

Comments

[piatachki]

Спасибо большое.

Answer 2

[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.

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

Comments

[piatachki]

Спасибо. Применить не удастся - в hql нет limit, но на jdbc работает в некоторой степени быстрее.