Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)?
Something like this, only it doesn't work :)
delete from table order by id ASC limit ((select count(*) from table ) - N)
You cannot delete the records that way, the main issue being that you cannot use a subquery to specify the value of a LIMIT clause.
This works (tested in MySQL 5.0.67):
DELETE FROM `table` WHERE id NOT IN ( SELECT id FROM ( SELECT id FROM `table` ORDER BY id DESC LIMIT 42 -- keep this many records ) foo );
The intermediate subquery is required. Without it we'd run into two errors:
Fortunately, using an intermediate subquery allows us to bypass both of these limitations.
NickC has pointed out this query can be optimised significantly for certain use cases (such as this one). I recommend reading his answer to see if it fits yours.