CODEWITHSUNDEEP
sqlsqlite
BlueWanderer
BlueWanderer

Reputation: 2691

How to select top N rows with the sum of certain column meet some requirement in SQLite?

If we have one table with a column called X contains int. Now I want to select (Actually I want to delete) the oldest N rows with the sum of X as much as possible but no more than 300?

  _id|X
    1|50
    2|150
    3|80
    4|100
    5|200
    6|70

Where 1 is oldest, 6 is newest. So I want to get row 1, 2, 3, whose sum of X is 280. Is it possible to achieve this with one single select statement?

Upvotes: 0

Views: 1525

Answers (1)

user610650
user610650

Reputation:

This should do:

.mode columns
.headers on

CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, value INT);
INSERT INTO foo (value) VALUES (50), (150), (80), (100), (200), (70);

SELECT t1.id, t1.value
FROM foo t1 INNER JOIN foo t2 ON t2.id <= t1.id
GROUP BY t1.id, t1.value
HAVING SUM(t2.value) <= 300
ORDER BY t1.id;

The SELECT returns:

id          value
----------  ----------
1           50
2           150
3           80

To delete the selected records:

DELETE FROM foo WHERE id IN (
    SELECT t1.id
    FROM foo t1 INNER JOIN foo t2 ON t2.id <= t1.id
    GROUP BY t1.id, t1.value
    HAVING SUM(t2.value) <= 300);

Upvotes: 1

Related Questions