Вывод MYSQLI с INNER JOIN и GROUP BY + ORDER. Как?

Question

[Gish]

Добрый день.
Есть TAB1
------------------------------------
    ID    |    RAW    |     RAW 2    |
------------------------------------
1
2
3
4
5

в данном случае интересен только ID.

Есть TAB2

------------------------------------
     ID     |    DATE    |     LOCAT    |
------------------------------------
    1    |   2020-05-05    |    AN    |
------------------------------------
    2    |   2020-05-15    |    ZR    |
------------------------------------
    3    |   2020-05-05    |    HT    |
------------------------------------
    2    |   2020-05-25    |    AN    |
------------------------------------
    1    |   2020-03-05    |    AN    |
------------------------------------
    2    |   2020-02-05    |   TR    |
------------------------------------
    1    |   2020-05-12    |    AN    |

Нужно сделать просто: взять ID из TAB1, сделать JOIN с TAB2, и вывести по порядку ID, LOCAT и DATE, но отсортировав по DATE - интересны только последние даты (свежие)

Т.е. получится на выходе

ID 1 - 2020-05-12 AN (последняя дата 2020-05-12)
ID 2 - 2020-05-25 AN (последняя дата 2020-05-25)
ID 3 - 2020-05-05 HT (Единственная дата - она же последняя)

Сейчас запрос выглядит так:

SELECT
tab1.id,
tab1.raw,
tab1.raw2,
tab2.id,
tab2.date,
tab2.locat,
FROM
`tab1`
JOIN `tab2` ON tab1.id = tab2.id
GROUP BY ID
ORDER BY id

Почти так, как надо, но проблема в том, что он при группировке по ID, он берет наименьшую дату, а мне надо наоборот - наисвежайшую. Как сортировать внутри GROP BY - хз.

Comments

[Максим Федоров]

свежайшая = наибольшая, соответственно не tab2.date, а MAX(tab2.date)

[Gish]

Максим Федоров, Пробовал - не получается.
В данном примере выведет только ID 3. Почему - не знаю

[Gish]

Gish, Точнее, почему - потому что там единственная запись такая. Но почему все остальные не выводит, не знаю

[Gish]

Непонятно, почему в данном случае не работает DISTINCT

Answer 1

[Andry]

Нужно сделать просто: взять ID из TAB1, сделать JOIN с TAB2, и вывести по порядку ID, LOCAT и DATE, но отсортировав по DATE - интересны только последние даты (свежие)

Нужно отобрать данные с T2, где для каждого ID выбрать запись с максимальной датой.

Если смысл наших фраз совпадает, то поступить нужно иначе. С tab2 данные нужно выбирать по двум полям (id, max(date), а у вас ключ лишь один - id. Значит сперва нужно сформировать набор данных с ключом из двух полей (id, date), а потом приджоинить к нему саму таблицу tab1, связав их сразу по двум полям. Получается выборка в два захода.

Как построить такой запрос очень зависит от масштабов данных. По вашим примерам не ясно, на что именно делать упор, потому приведу чисто школьный пример.

select 
  t1.id, t1.raw, t2.date, t2.locat
from ( -- формируем промежуточный набор данных с id+date подходящих записей 
select a2.id, max(a2.date) date
from tab2  a2 
  join tab1 a1 on a2.id = a1.id  -- этот джоин ограничивает множество a2, чтобы не группировать лишние записи
group by a1.id, a1.date
) n
join tab1 t1 on t1.id = n.id
join tab2 t2 on t2.id = n.id and t2.date = n.date -- теперь в джоине участвует два поля, чтобы точно указать на запись, которая нам нужна

Можете выполнить внутренний запрос отдельно и увидеть, что он вам выберет.

Проявите уважение к отвечающим - перечитайте свой вопрос перед публикацией, оцените "доходчивость" и полноту описания + используйте форматирование :)

Comments

[Gish]

Алиасы использую, в данном вопросе-примере их опустил.

"Добавьте агрегатные функции в выбираемым полям и всё станет на место. " - не понятно... Можете пример какой-нибудь привести, применимый к моему вопросу?

[Gish]

Делал и так:

SELECT
tab1.id,
tab1.raw,
tab1.raw2,
tab2.id,
MAX(tab2.date) AS lastdate,
tab2.locat,
FROM
`tab1`
JOIN `tab2` ON tab1.id = tab2.id
GROUP BY ID
ORDER BY id

Последнюю то дату он мне выводит, но не связанную с ID, который был записан в эту дату.

При Id 1 выведет Lastdate как 2020-05-12, но LOCAt - AN, как первую попавшуюся у данного ID.

Answer 2

[Rsa97]

При группировке все поля, не включённые в GROUP BY или в агрегатные функции берутся из первой попавшей в группу строки. А в стандарте SQL такие поля вообще запрещены (в MySQL 5.7.5 и выше поведение по умолчанию стало соответствовать стандарту).

SELECT *
  FROM (
    SELECT `id`, MAX(`date`) AS `max_date`
      FROM `tab2`
      GROUP BY `id`
  ) AS `max`
  JOIN `tab2` ON `tab2`.`id` = `max`.`id`
    AND `tab2`.`date` = `max`.`max_date`
  JOIN `tab1` ON `tab1_id` = `tab2_id`

В MySQL 8 добавили оконные функции, с ними решается проще

SELECT *
  FROM (
    SELECT DISTINCT FIRST_VALUE(`id`) OVER `win` AS `id`,
           FIRST_VALUE(`date`) OVER `win` AS `date`,
           FIRST_VALUE(`loc`) OVER `win` AS `loc`
      FROM `tab2`
      WINDOW `win` AS (PARTITION BY `id` ORDER BY `date` DESC)
  ) AS `t2`
  JOIN `tab1` ON `tab1`.`id` = `t2`.`id`

Comments

[Gish]

Не получилось

Answer 3

[Максим]

Для MySQL 8 (с оконными функциями)

select id, raw, raw2, D, locat
from (
SELECT
tab1.id,
tab1.raw,
tab1.raw2,
tab2.date D,
max(tab2.date) over (partition by tab1.id) max_D,
tab2.locat
FROM
`tab1`
JOIN `tab2` ON tab1.id = tab2.id) t
where D = max_D