Page 1 of 1

Select ROWS Until RUNNING TOTAL meet condition

PostPosted: Fri Dec 27, 2013 11:49 am
by xboss
One of the column in my table has Individual cost of an item.

Table: SAMPLE
NO. Item Cost ($)
1. A 30.00
2. B 50.00
3. C 40.00
4. D 25.00
5. E 15.00
6. F 35.00

Now I want to write a query to select rows from top
until I meet my budget of $100.00. In this case only
2 rows should be selected in my new table.

Any suggestion??

So far my query (not working offcourse)
SELECT ITEM, (SELECT SUM(COST) FROM SAMPLE S1 WHERE
   S1.NO = S2.NO AND S1.ITEM = S2.ITEM) AS RUNNINGTOTAL
             FROM SAMPLE S2;

Re: Select ROWS Until RUNNING TOTAL meet condition

PostPosted: Fri Dec 27, 2013 2:59 pm
by NicC
Use a cursor and loop through the returned rows until condition is met. By the way, why do you not expect item D in your result as it + A and B comes to $95.00?

Re: Select ROWS Until RUNNING TOTAL meet condition

PostPosted: Fri Dec 27, 2013 8:42 pm
by xboss
I haven't used cursor yet. So, that will be new thing for me to explore (thanks for that) but I am merely trying to accomplish this using SQL (if possible). I am not expecting item D in my result. I am only expecting Item A and B because going further down would exceed by budget. Thanks for your reply.

Re: Select ROWS Until RUNNING TOTAL meet condition

PostPosted: Sat Dec 28, 2013 3:06 am
by Terry Heinze
I suspect NicC was assuming your logic would skip items that causes your accumulation to exceed the budget, and would keep looking for items that would NOT exceed it.