SQL Query to return rows only when a max limit on SUM has been reached.

[rod]

Hello SQL gurus!

I am trying to write a query that will return a set of continguous rows
from a table, and limit the number of rows returned when a maximum
total has been reached by adding a value in one of the columns.

For example, the two columns below represent 2 columns in a table.

a 2
b 2
c 2
d 3
e 4
f 5
g 5

I want to start at, say "c", and return all the rows after it as long
as the sum of the numbers in column 2 (starting at "c") don't exceed
10. The result I'm after would be thus

c 2
d 3
e 4

....because 2 + 3 + 4 = 9 < 10

Any ideas? Many thanks.

[Madhivanan]

Try this
declare @t table(i char(10), id int)
insert into @t values('a',2)
insert into @t values('b',1)
insert into @t values('c',3)
insert into @t values('d',3)
insert into @t values('e',2)
insert into @t values('f',2)
insert into @t values('g',2)
insert into @t values('h',2)

select i, id from(
select i, id, (select sum(id) from @t where i<=T.i and i>='c') as 'Sum'
from @t T) M
where sum<=10

Madhivanan

[rod]

Madihivanan, many thanks for this. It works perfectly! You've made
me think more about nested sets and expanded my SQL knowledge.

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(sort_order CHAR(1) NOT NULL PRIMARY KEY,
val INTEGER NOT NULL);

I want to start at, say "c", and return all the rows after it as

long as the sum of the numbers in column 2 (starting at "c") don't
exceed 10. <<

SELECT @my_starter AS start, MAX(F1.sort_order) AS finish
FROM Foobar AS F1
WHERE @my_starter <= F1.sort_order
AND 10 <= (SELECT SUM(F2.val)
FROM Foobar AS F2
WHERE F2.sort_order
BETWEEN @my_starter AND F1.sort_order)

[rod]

Thanks Joe, however this SQL doesn't produce the result I'm after. The
SQL by Madhivanan above was spot on. Thanks anyway.