sql server - SQL: select rows where sum of a column satisfies a condition -

sql server - SQL: select rows where sum of a column satisfies a condition -

on [mytable] :

[id] int -- unique [price] money

on set ordered [id], need select [id]s sum of [price] meets condition

for example:

[id] [price] 1 2.0 2 4.7 3 3.2 4 2.8 5 6.2 6 1.5 7 4.2 8 3.3

for given number '10.0':

[id] [price] [r_total] 1 2.0 2.0 2 4.7 6.7 3 3.2 9.9 4 2.8 12.7 <-- here criteria meets 10.0 5 6.2 18.9 6 1.5 20.4 7 4.2 24.6 8 3.3 27.9

the desired result set of [id]s :

[id] 1 2 3 4

the problem solved using running total, main problem want avoid calculating running total set first, , find point criteria meets, , reason table contains more 100.000.000 rows, , given number comparing total sum of [price] little ( eg: 1250.14 ), , expected result barely riches 100-150 rows!

is there other way calculate , desired rows without disturbing 100.000.000 rows ?

please seek using cte:

;with cte1 ( select id, price, cost cum_sum yourtable id=1 union select c.id, c.price, c.price+c1.cum_sum cum_sum cte1 c1 inner bring together yourtable c on c.id=c1.id+1 10 >c1.cum_sum ) select * cte1

sql sql-server