CODEWITHSUNDEEP
sqlsqlite
Alexey Romanov
Alexey Romanov

Reputation: 170745

Limit by running total in SQLite

Let's say I have a table like this (ordered by id):

id    amount
---   ---
1     10
2     15
3     10
4     30

I want a query which will return rows such that the sum of amount is greater than a given number. So (in a non-existing syntax) SELECT id, amount LIMIT BY running_total(amount) 20 selects first 2 rows, ... LIMIT BY running_total(amount) 60 selects all rows. I can't change the schema to keep the running total precomputed. Can this be done reasonably efficiently? It would be acceptable if the answer works only on SQLite.

Upvotes: 2

Views: 574

Answers (1)

Andomar
Andomar

Reputation: 238116

You could use a subquery that sums all rows with a lower id:

select  *
from    YourTable t1
where   20 > coalesce(
        (
        select  sum(amount)
        from    YourTable t2
        where   t2.id < t1.id
        ), 0)

The coalesce is to catch the first row, which has a sum of null.

Upvotes: 1

Related Questions