Android sqlite, limit number of rows in database [

问题:

I have new data coming into the app all the time, so I want to limit the number of rows in a table to, say, 100 records. I would describe it as a FIFO queue. Whenever there new data (just a few rows a time) coming in, old data at the 'bottom' of the table are flushed out and deleted. Since it's FIFO, I don't want to manually perform a sort, then delete, then insert back in. I guess there must be cheap way to do this, right? Thanks

回答1:

A query like this will show all recors, newest first:

SELECT *
FROM MyTable
ORDER BY Date DESC    -- or some autoincrementing ID column

With an OFFSET clause, you can skip the first records. This means that you get all records except the first 100 ones, i.e., you get those records that should be deleted:

SELECT *
FROM MyTable
ORDER BY Date DESC
LIMIT -1 OFFSET 100

You can then use this in a subquery to actually delete the records:

DELETE FROM MyTable
WHERE ID IN (SELECT ID
             FROM MyTable
             ORDER BY Date DESC
             LIMIT -1 OFFSET 100)