Steve Krulewitz <[EMAIL PROTECTED]> wrote:
I have a table that I would like to select the first N records where
the rolling sum of a given column is less than some literal value.
SQL for this might look like:

select item_id from items where rolling_sum(item_size) < 10000 order
by item_name;

Is there a way to do this?

select item_id,
(select sum(item_size) where t2.item_name <= t1.item_name from tableName t2) rs
from tableName t1
where rs < 10000
order by item_name;

It'll probably run very slow for any but small datasets.

Would sqlite know to stop processing the
query once the limit is reached?

Unlikely. It would require heroic efforts on the part of optimizer to figure out that the rolling sum column grows monotonously.

Manually doing the rolling sum while
reading the query result is not a good option for me as I am working
with an API on top of sqlite that does not allow this.

I am already using a custom collation sequence, so adding a custom
function would not be a problem :)

A custom function would have the same problem - SQLite engine wouldn't be able to stop early. But at least it would run in O(N) rather than O(N^2).

Igor Tandetnik

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to