Как верно сгруппировать данные в запросе?

Question

[Петр Питерский]

Есть набор id, по каждому из них индивидуально надо найти последнюю его запись. Такой вариант не проходит:

SELECT `good` AS `id`, `price`, `date` FROM `item_good_supplies` AS `s` WHERE `good` = (SELECT MAX(`date`) FROM `item_good_supplies` WHERE `good` = `s`.`good`) AND `good` IN('23068', '21805', '23204', '22493', '21813', '21802', '23845')

В чем затык? По отдельности сами запросы работают корректно.

Answer 1

[Rsa97]

Для MySQL 8.0 и выше.

WITH `cte` (`id`, `price`, `date`, `row`) AS (
  SELECT `good`, `price`, `date`, ROW_NUMBER() OVER `win`
    FROM `item_good_supplies`
    WHERE `good` IN('23068', '21805', '23204', '22493', '21813', '21802', '23845')
    WINDOW `win` AS (
      PARTITION BY `good`
      ORDER BY `date` DESC
    )
)
SELECT `id`, `price`, `date`
  FROM `cte`
  WHERE `row` = 1

Answer 2

[Александр]

Затык в том, что условие WHERE построено не верно. Вложенный запрос вернет MAX('date') и это дата, а сравнивается это значение с good (который как я понимаю id)
Я не знаю ваших данных, но предположу что в таблице item_good_supplies содержатся записи с одинаковым полем good и разной датой. В этом случае почему бы не воспользоваться group by ?:

SELECT `good` AS `id`, /* `price`, */ MAX(`date`) as `date` 
FROM `item_good_supplies` 
WHERE `good` IN('23068', '21805', '23204', '22493', '21813', '21802', '23845')
GROUP BY `good`

Update:
Как справедливо указал Rsa97 в комментарии, в этот запрос не получится впихнуть цену, т к good не является первичным ключом, а без этого необходимо указать price в group by, что приведёт к неверному результату. Если в таблице первичного ключа нет, то будет затруднительно составить запрос, который подставит корректную цену.

Comments

[Rsa97]

Предположим, у нас десять строк с одним и тем же `good` и разными `price`. Какое именно значение `price` должно попасть в результат группировки и почему?
Ну и, в строгом режиме этот запрос просто выдаст ошибку. Все выбираемые поля должны быть либо в GROUP BY, либо в агрегатных функциях.

[Александр]

Rsa97, Да, Вы правы. Так не сработает. И что то я затрудняюсь написать запрос, который бы сработал корректно без уникального первичного ключа.

[Rsa97]

Александр,

SELECT `t2`.*
  FROM (
    SELECT `good`, MAX(`date`) AS `maxdate`
      FROM `item_good_supplies`
      WHERE `good` IN('23068', '21805', '23204', '22493', '21813', '21802', '23845')
      GROUP BY `good`
  ) AS `t1`
  JOIN `item_good_supplies` AS `t2`
    ON `t2`.`good` = `t1`.`good` AND `t2`.`date` = `t1`.`maxdate`

[Александр]

Rsa97, О таком варианте я конечно подумал и даже уже почти дописал... Но тут получается, что если будут две одинаковых даты, то тоже может получится не то что нужно.
И ещё я бы к внешнему запросу добавил условие `good` IN('23068', '21805', '23204', '22493', '21813', '21802', '23845') для сокращения количества строк JOIN

[Rsa97]

Александр,

если будут две одинаковых даты, то тоже может получится не то что нужно.

Да. Но тогда никакой запрос не сможет выдать "последнее" значение. В частных случаях можно полагаться на монотонность автоинкрементного поля, если таковое есть, но, в общем случае, последнее добавленное не обязательно будет с последним временем.

А по поводу добавления IN во внешний WHERE - надо смотреть EXPLAIN. Возможно, что это добавление ничего не даст.

[Александр]

Rsa97, Нашёл аналогичный вопрос на Stackoverflow: https://stackoverflow.com/questions/49404/sql-quer...
В принятом ответе ризонно предлагают добавить колонку `isCurrent`. И я с этим соглашусь. В этом случае запросы драмматически упрощаются.