SQL select top X rows having sum limit

Question

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 :

Input :

1. category (lets say 2).
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.

EDIT:

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)

Answer 1

Just Join the table to itself and make the appropriate sum.

This query will match each row in the table to all rows in the table with a smaller ID and the same category. You can then easily sum these rows and filter out all rows with a total greater than your limit.

Using sub queries in the where clause like you do might result in many unnecessary query executions if your sub-queries are not optimized to an INNER JOIN by the database engine:

DECLARE @Limit FLOAT
SELECT @Limit = 30

SELECT
    o1.Id
    , o1.Cost
    , o1.Category
FROM
    MyOrders o1
    -- Join all orders with same category and inferior ID
    INNER JOIN MyOrders o2 ON o2.ID <= o1.ID AND o1.Category = o2.Category
GROUP BY
    o1.Id
    , o1.Cost
    , o1.Category
HAVING
    SUM(o2.Cost) < @Limit
ORDER BY
    ID