Rolling Maximum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rolling Maximum

Author

Topic

priestlm
Starting Member

13 Posts

Posted - 2010-07-29 : 06:19:36

Hi all - im sure there is a quick & easy answer to this but i cant seem to find anything.

Can someone please let me know how to limit a select query to return a number of rows where the QTY does not exceed a predefined value.

For example, for the below table a select statment which has a limit of 10 would return rows A & B but ignore the rest.

******
ID - QTY
A - 4
B - 6
C - 8
D - 2
******

Thanks
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-29 : 06:22:31

What if the limit is 9 or 11, what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

priestlm
Starting Member

13 Posts

Posted - 2010-07-29 : 06:27:51

if the limit was 11 then it would return a result set up that as a maximum, so in the above case it would return A & B. If the limit was 9 it would return only A. The query would have always select the top row & then work through the rows in sequence, so for example if the limit was 6 the rows return would be A & D not just B.

Thanks
Mark

priestlm
Starting Member

13 Posts

Posted - 2010-07-29 : 06:29:07

Sorry if the limit was 9 it would return A & D

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-29 : 06:30:49

try this:


create table test
(id char(1)
,qty int
)
insert test
select 'A',4
union all select 'B',6
union all select 'C',8
union all select 'D',2

--SOLUTION
select
x.id
,x.s_Q
,x.running_sum
from
(
select
	t.id
	,Sum(t.qty) as s_Q
	,(select sum(t2.qty) from test as t2
	where t2.id<=t.id
					)  as running_sum
from test as t
group by t.id
) as x
where x.running_sum <= 10

priestlm
Starting Member

13 Posts

Posted - 2010-07-29 : 06:35:02

Thats perfect - thank you Slimt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources