472,796 Members | 1,586 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,796 software developers and data experts.

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

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 10269

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


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

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Mike Cocker | last post by:
Hello, I'm quite weak at PHP, so I was hoping to get some help understanding the below code. First off, I'm trying to create a "query form" that will allow me to display the results on my...
by: Jesse Sheidlower | last post by:
I'm struggling with speed issues on some queries that I would have expected to be relatively fast. Perhaps even more frustratingly, when I've tried to break these down into their components, they...
by: unixman | last post by:
As usual, it is 2:00am, and I'm pulling my hair out, finally resorting to posting in the newsgroups for help. :) Simple problem, in theory. Given table "map": CREATE TABLE map ( entry_id...
by: emily_g107 | last post by:
Hi, I need to limit results in the following query type: ...
by: Dmitry Tkach | last post by:
Hi, everybody! Here is a weird problem, I ran into... I have two huge (80 million rows each) tables (a and b), with id as a PK on both of them and also an FK from b referencing a. When I try to...
by: Egor Shipovalov | last post by:
I'm implementing paging through search results using cursors. Is there a better way to know total number of rows under a cursor than running a separate COUNT(*) query? I think PostgreSQL is bound...
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on...
by: oddemann | last post by:
Hi. I have a problem that I can`t find solution to. The errorlog i full of this. 01/11 15:54:15.36 sdpprim sql_agent 27772 SIGNIFICANT sqlsrvr.c 2136 The limit set by...
by: bilibytes | last post by:
Hello i'm intrigued by what happens behind the scenes when I do a DB query to a database from php. I would like to know what is the exact life cycle of a mysql_query() I ask this question...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.