How to delete old record, leaving at least one final for each sensor?

Table channel_snapshots written status reports from different sensors (snapshot_time, FK channel_id). To keep the table size manageable, old records more than 12 hours to remove, but it was not removed the last of the old report of the sensor, which for some reason has not sent the report. That is, you need to leave 1 the latest report from each sensor, from which no report was sent over 12 hours ago.

At the moment, removed all the corny old records more than 12 hours, and even those that you need to write the piece.
Question: can I perform this task in a single query, given this logic? If so, how?
July 2nd 19 at 13:35
3 answers
July 2nd 19 at 13:37
Solution
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
Thank you! - alfonso56 commented on July 2nd 19 at 13:40
July 2nd 19 at 13:39
In the Oracle dialect of SQL:
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
 );
Why? - alfonso56 commented on July 2nd 19 at 13:42
: Sergey, why did I do what? - samir39 commented on July 2nd 19 at 13:45
: why the Oracle dialect, if the author of MySQL? - francisco.Douglas commented on July 2nd 19 at 13:48
faster LEFT JOIN or NOT IN as ? - samir39 commented on July 2nd 19 at 13:51
I meant the opposite - alfonso56 commented on July 2nd 19 at 13:54
: EXPLAIN'om should look. - alfonso56 commented on July 2nd 19 at 13:57
July 2nd 19 at 13:41
first, it is necessary to ID the top entries. grouping by type of sensor, then remove everything but those
DELETE `tab` WHERE `id` NOT IN (SELECT max(id) FROM `tab` GROUP BY `channel`)

Find more questions by tags DatabasesMySQLProgrammingSQL