Select x rows from table having total < y SQL Server

Question

I have Table A with the following data:

Id  Value
1   100
2   63
4   50
6   24
7   446

I want to select the first rows with a SUM(value) <= 200.

So the desired output should be:

Id Value
1   100
4   50
6   24

Answer 1

SIMPLE ANSWER

You need to find the Cumulative Sum for each row, and since you want as many rows as possible, you need to start with the lowest value (ORDER BY Value):

WITH Data AS
(   SELECT  Id, 
            Value,
            CumulativeValue = SUM(Value) OVER(ORDER BY Value, Id)
    --FROM  (VALUES (1, 100), (2, 300), (4, 50), (6, 24), (7, 446)) AS t (Id, Value)
    FROM    TableA AS t
)
SELECT  d.Id, d.Value
FROM    Data AS d
WHERE   d.CumulativeValue <= 200
ORDER BY d.Id;  

COMPLETE ANSWER

If you want to be more selective about which rows you choose that have a sum of less than 200 then it gets a bit more complicated for example, in your new sample data:

Id  Value
1   100
2   63
4   50
6   24
7   446

There are 3 different combinations that allow for a total of less than 200:

Id  Value
1   100
2   63
6   24

--> 187

Id  Value
2   63
4   50
6   24

--> 137

Id  Value
1   100
4   50
6   24

--> 174

The only way to do this is to get all combinations that have a sum less than 200, then choose the combination that you want, in order to do this you will need to use a recursive common table expression to get all combinations:

WITH TableA AS
(   SELECT  Id, Value
    FROM    (VALUES (1, 100), (2, 63), (4, 50), (6, 24), (7, 446)) t (Id, Value)
), CTE AS
(   SELECT  Id,
            IdList = CAST(Id AS VARCHAR(MAX)), 
            CumulativeValue = Value,
            ValueCount = 1
    FROM    TableA AS t
    UNION ALL
    SELECT  T.ID, 
            IdList = CTE.IDList + ',' + CAST(t.ID AS VARCHAR(MAX)),
            CumulativeValue = CTE.CumulativeValue + T.Value,
            ValueCount = CTE.ValueCount + 1
    FROM    CTE
            INNER JOIN TableA AS T
                ON ',' + CTE.IDList + ',' NOT LIKE '%,' + CAST(t.ID AS VARCHAR(MAX)) + ',%'
                AND CTE.ID < T.ID
    WHERE   T.Value + CTE.CumulativeValue <= 200
)
SELECT  *
FROM    CTE
ORDER BY ValueCount DESC, CumulativeValue DESC;

This outputs (with single rows removed)

Id  IdList  CumulativeValue ValueCount
-------------------------------------
6   1,2,6       187         3
6   1,4,6       174         3
6   2,4,6       137         3
2   1,2         163         2
4   1,4         150         2
6   1,6         124         2
4   2,4         113         2
6   2,6         87          2
6   4,6         74          2

So you need to choose which combination of rows best meets your requirements, for example if you wanted, as described before, the most number of rows with a value as close to 200 as possible, then you would need to choose the top result, if you wanted the lowest total, then you would need to change the ordering.

Then you can get your original output by using EXISTS to get the records that exist in IdList:

WITH TableA AS
(   SELECT  Id, Value
    FROM    (VALUES (1, 100), (2, 63), (4, 50), (6, 24), (7, 446)) t (Id, Value)
), CTE AS
(   SELECT  Id,
            IdList = CAST(Id AS VARCHAR(MAX)), 
            CumulativeValue = Value,
            ValueCount = 1
    FROM    TableA AS t
    UNION ALL
    SELECT  T.ID, 
            IdList = CTE.IDList + ',' + CAST(t.ID AS VARCHAR(MAX)),
            CumulativeValue = CTE.CumulativeValue + T.Value,
            ValueCount = CTE.ValueCount + 1
    FROM    CTE
            INNER JOIN TableA AS T
                ON ',' + CTE.IDList + ',' NOT LIKE '%,' + CAST(t.ID AS VARCHAR(MAX)) + ',%'
                AND CTE.ID < T.ID
    WHERE   T.Value + CTE.CumulativeValue <= 200
), Top1 AS
(   SELECT  TOP 1 IdList, CumulativeValue
    FROM    CTE
    ORDER BY ValueCount DESC, CumulativeValue DESC -- CHANGE TO MEET YOUR NEEDS
)
SELECT  *
FROM    TableA AS t
WHERE   EXISTS
        (   SELECT  1
            FROM    Top1
            WHERE   ',' + Top1.IDList + ',' LIKE '%,' + CAST(t.ID AS VARCHAR(MAX)) + ',%'
        );

This is not very efficient, but I can't see a better way at the moment.

This returns

Id  Value
1   100
2   63
6   24

This is the closest to 200 you can get with the most possible rows. Since there are multiple ways of achieving "x number of rows that have a sum of less than 200", there are also multiple ways of writing the query. You would need to be more specific about what your preference of combination is in order to get the exact answer you need.

Answer 2

This should return expected result:

WITH TableWithTotals AS
(SELECT 
 id, 
 value,
 SUM (Value) OVER (ORDER BY Value, Id) as total
FROM myTable)
SELECT * FROM TableWithTotals
WHERE total <=200;

This code will maximise the number of records that fit under 200 limit as running total is calculated over ordered values.

SQL Fiddle

Answer 3

As per i understood your requirement, i think below query should be helpful to you.

WITH FinalResult AS
(SELECT 
 id, 
 value,
 SUM (Value) OVER (ORDER BY Value, Id) as ValueCount
FROM TableA)
SELECT * FROM FinalResult
WHERE ValueCount <=200;

Answer 4

you should try this:-

    SELECT Id, Value FROM TableName WHERE Value <=200