Rolling database entries by some criteria

Question

I have a database with up to some n entries allowed. Periodically, it needs to be trimmed down to the most recent m entries.

The table (call it mytable) has a datetime stamp runstamp

My general thought was to run a query like this

delete from mytable where runstamp <
   (select min(runstamp) from mytable order by runstamp limit m)

But it turns out that the max is taken before the limit is applied, and I'm not sure how to get the job done now.

Implementation detail that may not matter: this is being done in sqlite.

Answer 1

I'm afraid that what you tried cannot possibly work.

The sub-select is supposed to return 1 row only: this is the minimal runstamp in the table. It follows, that no row in mytable has a runstamp that is lower, so nothing gets deleted.

Perhaps you want something like:

(select max(runstamp) from 
   (select runstamp from mytable order by runstamp limit m)) 

Answer 2

A perl-formatted query that solved my issue, sorta based off of blissapp's link.

   my $truncate_query = "DELETE FROM mytable WHERE runstamp < " .
       "(SELECT MIN(runstamp) FROM " .
       "(SELECT * FROM mytable ORDER BY runstamp DESC LIMIT $max_records))";