sqlite filtering by sum

Question

I have a database with 1000 records containing a file name and a file size on each row. If the SUM of all file sizes exceeds a specific limit, then: - I need to create a sql query to delete all the remaining rows, from oldest to newest

Any suggestions? Thanks

Answer 1

Do binary search. Check if sum of 500 newest files is less then limit, if so, if sum of 750 is below limit etc. Until you get the row from which you should start deleting. Then just DELETE WHERE file_date > date_of_your_last_row_below_limit.

Answer 2

Probably the easiest solution (that is still fast even with many rows) is to calculate the running total in the application:

select createdDate, size from files order by createdDate desc

Now read the result set, and in the loop use total += size. Once total is larger, delete everything older than the current createdDate:

delete from files where createdDate < ?

Some other databases (for example MySQL and H2) support efficient running totals, but not SQLite.

Answer 3

Supposing a table created thus:

CREATE TABLE Files (Id INTEGER PRIMARY KEY, FileName TEXT, CreationDate DATE, Size INTEGER);

To get the running sum, use the following query:

SELECT f1.id AS FileId, sum(f2.size) AS RunningSumSize
FROM file f1 INNER JOIN file f2
ON f1.createdDate<=f2.createdDate
GROUP BY FileId
ORDER BY RunningSumSize DESC;

To delete the file ID's above the threshold:

DELETE FROM File WHERE Id IN
 (SELECT FileId FROM 
  (SELECT f1.id AS FileId, sum(f2.size) AS RunningSumSize
  FROM file f1 INNER JOIN file f2
  ON f1.createdDate<=f2.createdDate
  GROUP by FileId
  ORDER by RunningSumSize DESC)
  WHERE RunningSumSize > :ThresholdSize:);

Note: The order by is optional.