MYSQLI output with INNER JOIN and GROUP BY + ORDER. How?

Question

PHP

Good afternoon.
There is TAB1
------------------------------
    ID | RAW | RAW 2 |
------------------------------------
1
2
3
4
5

in this case, only the ID is of interest.

There is 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 | You need to do it simply: take ID from TAB1, do JOIN with TAB2

, and display ID, LOCAT and DATE in order, but sorted by DATE - only the latest dates (fresh) are interesting.

output

ID 1 - 2020-05-12 AN (last date 2020-05-12)
ID 2 - 2020-05-25 AN (last date 2020-05-25)
ID 3 - 2020-05-05 HT (The only date - it is the last one)

Now the query looks like this:

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

Almost right, but problem in that, when grouped by ID, it takes the smallest date, but I need the opposite - the freshest. How to sort inside GROP BY - xs.

Answer 1

You need to do it simply: take the ID from TAB1, do a JOIN with TAB2, and print ID, LOCAT and DATE in order, but sorted by DATE - only the latest dates (fresh) are interesting

It is necessary to select data from T2, where for each ID select a record with the maximum date.
If the meaning of our phrases is the same, then you need to act differently. With tab2, you need to select data by two fields (id, max(date), and you have only one key - id. So, first you need to form a data set with a key from two fields (id, date), and then join the tab1 table itself to it , connecting them by two fields at once. It turns out a selection in two passes.
How to build such a query depends very much on the scale of the data. Based on your examples, it is not clear what exactly to focus on, therefore I will give a purely school example.

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 -- теперь в джоине участвует два поля, чтобы точно указать на запись, которая нам нужна

You can run the inner query separately and see what it chooses for you.
Show respect for the answerers - re-read your question before posting, evaluate the "intelligibility" and completeness of the description + use formatting :)

Answer 2

When grouping, all fields not included in GROUP BY or in aggregate functions are taken from the first row in the group. And in the SQL standard, such fields are generally prohibited (in MySQL 5.7.5 and higher, the default behavior became consistent with the standard).

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 added window functions, they are easier to solve

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`

Answer 3

For MySQL 8 (with window functions)

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