How to select recent records by a specific field?

Question

Laravel

Good afternoon.
It is necessary to select from the mySQL database of the `keys` table only the latest data for the `serial` field:

## таблица keys
serial | event_date_time | rest_data..
------ | --------------- | -----------
 sn005 | 2015-05-05      | data1
 sn001 | 2015-01-01      | data1
 sn001 | 2016-06-06      | data2
 sn001 | 2020-10-10      | data3
 sn005 | 2019-09-09      | data2

serial | event_date_time | rest_data..
------ | --------------- | -----------
 sn001 | 2020-10-10      | data3
 sn005 | 2019-09-09      | data2

So far this has come close to:

SELECT t.* FROM 
(SELECT * FROM `keys` ORDER BY event_date_time DESC) t 
GROUP BY t.serial

but even here the date is not the last ...
I have two questions:
1. Please help with the request.
2. How to execute it in Laravel/Eloquent way?
Thank you. Gone to smoke the docks ...

Answer 1

SELECT `k`.*
  FROM (
    SELECT `serial`, MAX(`event_date_time`) AS `event_date_time`
      FROM `keys`
      GROUP BY `serial`
  ) AS `d`
  JOIN `keys` AS `k`
    ON `k`.`serial` = `d`.`serial` AND `k`.`event_date_time` = `d`.`event_date_time`

Answer 2

If there is an id Primary Key, then you can choose from it, without the participation of event_date_time

SELECT *
FROM keys
WHERE id IN (SELECT MAX(id) AS id
             FROM keys
             GROUP BY serial)