How can I delete old entries, leaving at least one latest for each sensor?

Question

MySQL

Status reports from different sensors are written to the channel_snapshots table ( snapshot_time , FK channel_id ). To keep the table size manageable, old records over 12 hours old should be deleted, but in such a way that the last old sensor report is not deleted, which for some reason did not send a report. That is, you need to leave 1 last report of each sensor from which a report was not sent more than 12 hours ago.
At the moment, all old records over 12 hours old are deleted, and even those that need to be left individually.
Question: Is it possible to accomplish this task with a single request, given the given logic? If so, how?

Answer 1

DELETE `t1`
  FROM `channel_snapshots` AS `t1`
  LEFT JOIN (
    SELECT MAX(`snapshot_time`) AS `time`, `channel_id`
      FROM `channel_snapshots`
      GROUP BY `channel_id`
  ) AS `t2` ON `t2`.`time` = `t1`.`snapshot_time` AND `t2`.`channel_id` = `t1`.`channel_id`
  WHERE `t1`.`snapshot_time` < NOW() - INTERVAL 12 HOUR 
    AND `t2`.`channel_id` IS NULL

Answer 2

In Oracle SQL dialect:

DELETE
FROM
  ALL_SNAPSHOTS
WHERE
  SNAPSHOT_TIME                       <=SYSDATE-12/24
  AND (SNAPSHOT_TIME, ID_CHANNEL) NOT IN
  (
    SELECT MAX(SNAPSHOT_TIME), ID_CHANNEL FROM ALL_SNAPSHOTS GROUP BY ID_CHANNEL
  );

Answer 3

first you need to get the id of the top records. grouping by sensor type, then delete everything except these

DELETE `tab` WHERE `id` NOT IN (SELECT max(id) FROM `tab` GROUP BY `channel`)