469,603 Members | 2,069 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

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.

Jul 23 '05 #1
4 9644

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

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

Jul 23 '05 #3
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)

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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mike Cocker | last post: by
reply views Thread by Jesse Sheidlower | last post: by
reply views Thread by unixman | last post: by
4 posts views Thread by emily_g107 | last post: by
13 posts views Thread by Dmitry Tkach | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.