I am using SQL Server 2012,
I have a table with a format such as :
MyOrders : ID -> PK int Cost -> float Category -> int
I want to get the following :
- category (lets say 2).
- sum_limit (lets say 500).
I need to get :
Top X (need to find x) rows from MyOrders, filtered by category (lets say where category = 2) having sum of cost smaller than the sum limit (lets say 500) but the largest that is smaller than the limit, ordered by ID.
In other words, go over the rows one by one (ordered by ID) who has category 2, and summarize the cost until i pass the cost limit, than return all those rows except the last one (that caused me to pass the limit).
Also i want to avoid using loops.
I thought of creating a query that selects a number (from 1 to the number of rows in MyOrders after filtering the category) and its sum of cost as the first stage,
I created a numbers table (https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) And than tried some queries but could not get it exactly.
Thanks for any help.
OK I used the following to get what i wanted :
select top 1 Number from NumbersTest where Number <= (select count(1) from MyOrders where category = 2) and (select sum(cost) from MyOrders where ID in (select top(Number) ID from MyOrders where category = 2 order by ID desc)) < cost_limit order by Number desc
Where NumbersTest is a table created as mentioned before (https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table)