Keep only N last records in SQLite database, sorted by date

To delete all but the latest 10 records.

deletefrom testwhere id not in (    select id    from test    order by date desc    limit 10)

According to the SQLite documentation:

If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses.

(...)

If the DELETE statement has an ORDER BY clause, then all rows that would be deleted in the absence of the LIMIT clause are sorted according to the ORDER BY. The first M rows, where M is the value found by evaluating the OFFSET clause expression, are skipped, and the following N, where N is the value of the LIMIT expression, are deleted. If there are less than N rows remaining after taking the OFFSET clause into account, or if the LIMIT clause evaluated to a negative value, then all remaining rows are deleted.

This would allow you to write:

DELETE FROM table WHERE expr ORDER BY date DESC LIMIT -1 OFFSET 10

to keep only the last 10 records, think inverted.

To delete the older 10 records:

DELETE FROM Table_name WHERE date in (SELECT date FROM Table_name ORDER BY Date Desc Limit -1                OFFSET  (select count(*)-10 from Table_name) );

Let me know how it worked for you!