Delete all but latest 500 rows from sqlite database

Question

My SQL isn't bad but SQLite on iOS constantly catches me out.

I have a table where I want to limit the number of rows to around 500, so when it hits a trigger point of say 550 it will delete the earliest 50 rows.

It's rolling so with time the id wont always start at one and the user can delete rows so the id is non-sequential.

I have a juliandate field (double) but I'm not sure that's any use

DELETE FROM contents WHERE id > '0' ORDER BY id DESC LIMIT 0, 50
DELETE FROM contents ORDER BY id DESC LIMIT 0, 50

Documentation says that's OK but it fails. Any ideas?

Answer 1

Keep latest 500 records:

delete
from table_name
where _id not
  in (select _id
    from table_name
    order by
      _id desc
    limit 500)

Answer 2

something along these lines, maybe?

delete from contents where juliandate <= (
    select max(juliandate) from (
        select juliandate from contents order by juliandate limit 0, 50));

You can use id instead of juliandate or any other field which value increases with every insert.