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

sql sqlite sql-delete
ahmd0 picture ahmd0 · Jun 30, 2011 · Viewed 13.8k times · Source

I have an SQLite database that I need to do the following: Keep only last N records, sorted by date. How do you do that?

Answer

Jacob Eggers picture Jacob Eggers · Jun 30, 2011

To delete all but the latest 10 records.

delete
from test
where id not in (
    select id
    from test
    order by date desc
    limit 10
)

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More