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

Question

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?

Need the columns, if any are autoincrement... – OMG Ponies Jun 29 '11 at 22:53 — OMG Ponies, Jun 29 '11 at 22:53
Duplicate: http://stackoverflow.com/q/1977341/132382 – pilcrow Jul 14 '11 at 03:02 — pilcrow, Jul 14 '11 at 03:02

Jacob Eggers · Accepted Answer · 2012-05-25T13:34:48.930

41

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
)

edited May 25 '12 at 13:34

answered Jun 29 '11 at 22:58

Jacob Eggers

9,062
2
25
43

Thanks. But how would I know how many records I need to delete? – ahmd0 Jun 29 '11 at 23:00
3

You don't, that query deletes all but the latest 10 records – Kieren Johnstone Jun 29 '11 at 23:05
Thanks, it worked. Although your sql statement doesn't need * – ahmd0 Jun 30 '11 at 01:18
1

Isn't it "...order by date desc limit 10"? – Drejc Feb 16 '12 at 21:24
I tried this exact query (cut and paste) and got an error returned. I then tried putting "desc" after "order by date" (as suggested by @Drejc) and it worked. – cheshirekow May 24 '12 at 15:02
@KierenJohnstone I was using datetime("XYZ") to sort which deleting the latest result. Removing it give the result fine. I am saving the string of format yyyy-MM-dd HH:mm:ss – Shubham AgaRwal Jan 17 '19 at 17:36

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 09 '12 at 19:07

Martín Valdés de León

1,318
10
8

~"If there are less than N rows remaining". What does -1 mean here? – IgorGanapolsky Dec 13 '13 at 16:00
@IgorGanapolsky Look at the last sentence of the quoted paragraph: _if the LIMIT clause evaluated to a negative value, then all remaining rows are deleted_. – Martín Valdés de León Dec 13 '13 at 20:39
So if there are less than 10 rows in the table altogether, then all of them will be deleted? – IgorGanapolsky Dec 14 '13 at 17:27
1

@IgorGanapolsky If there are less than 10 rows in the table altogether, then none of them will be deleted. – Martín Valdés de León Dec 15 '13 at 14:57
@VladimirCh maybe that's because this question is for SQLite, not mariaDB. – Évelyne Lachance Sep 14 '18 at 04:19

score 2 · Answer 3 · edited Mar 04 '13 at 00:35

2

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!

edited Mar 04 '13 at 00:35

Rui Jarimba

11,166
11
56
86

answered Mar 04 '13 at 00:17

Marcelo Saied

21
1

Muzikant · Answer 4 · 2014-05-21T17:43:04.823

2

Assuming you have an id column which is a sequential number (AUTO INCREMENT), you can use the following:

DELETE FROM table_name
WHERE id < (
    SELECT MIN(id)
    FROM (SELECT id
          FROM table_name
          ORDER BY id DESC
          LIMIT num_of_records_to_keep))

The same query can be used when using a timestamp column (simply replace id with your timestamp column)

edited May 21 '14 at 17:43

answered May 21 '14 at 16:01

Muzikant

8,070
5
54
88

score 0 · Answer 5 · answered Oct 03 '21 at 14:41

0

I wonder if this is faster or slower than Jackob solution. sqlitebrowser says 0ms for both...

DELETE FROM test WHERE date < ( SELECT date FROM test ORDER BY date DESC LIMIT 1 OFFSET 9 )

answered Oct 03 '21 at 14:41

Jackt

171
1
1
15

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

5 Answers5

Linked