Select top X from table where SUM(column) <= @variable

I'm trying to select the top n number of rows where the sum of a column is less than or equal to a number i pass in as a variable.

TableX:

Col1  Col2
1     1开发者_运维技巧0
2     10
3     5
4     20

So basically i'm trying to do something like this:

SELECT * FROM TableX WHERE SUM(Col2) <= 25

And I would be expecting the result:

Col1  Col2
1     10
2     10
3     5

---

You're looking for the HAVING clause.

Unfortunately, I don't have access to a sql server at this time to test a complete solution but... in mysql this seems to do exactly what you want, and it should also do it for T-SQL if I am not mistaken.

SELECT A.Col1, A.Col2, SUM(B.Col2) AS CumulativeCol2
FROM TableX A
INNER JOIN TableX B ON B.Col1 <= A.Col1
GROUP BY A.Col1
HAVING SUM(B.Col2) <= 25

returns:

Col1    Col2    CumulativeCol2
1       10      10
2       10      20
3       5       25

---

declare @table as table(Col1 int, Col2 int)
insert into @table values (1, 10)
insert into @table values (2, 10)
insert into @table values (3, 5)
insert into @table values (4, 20)



 ;with TableWithIndex(Row, Col1, Col2)
 as
 (
   select row_number() over(order by Col1) Row, Col1, Col2
   from @table   
 ),
 ColTable(Row, Col1, Col2, Col2Sum)
 as (
   select Row, Col1, Col2, Col2 Col2Sum
   from TableWithIndex
   where Row = 1 and Col2 <= 25
   union all
   select ti.Row, ti.Col1, ti.Col2, ti.Col2 + Col2Sum
   from TableWithIndex ti
   inner join ColTable ct on ct.Row + 1 = ti.Row   
   where ti.Col2 + Col2Sum <= 25
 )
 select Col1, Col2
 from ColTable
 option (maxrecursion 0)

---

Here's what you need (including proof of concept):

declare @t table(Col1 int, Col2 int)

insert into @t values
(1, 10),
(2, 10),
(3, 5),
(4, 20),
(5, 3)

select * from
(
    select t1.Col1, t1.Col2, SUM(t2.Col2) as runningtotal
    from @t t1
    inner join @t t2 on t1.Col1 >= t2.Col1
    group by t1.Col1, t1.Col2
) ss
where runningtotal <= 25

---

Edit: Please do not confuse the values from TestDataID identity(1,1) column with RowNumber values​​. There is no link between this values.

This solution is using recursive CTEs:

CREATE TABLE TestData
(
     TestDataID INT IDENTITY(2,2) PRIMARY KEY
    ,ProductID  INT NOT NULL
    ,SalesQty   INT NOT NULL
);

INSERT  TestData 
SELECT  1, 10
UNION ALL
SELECT  1, 10
UNION ALL
SELECT  1, 5
UNION ALL
SELECT  1, 11

UNION ALL
SELECT  2, 20
UNION ALL
SELECT  2, 2
UNION ALL
SELECT  2, 10

UNION ALL
SELECT  3, 27
UNION ALL
SELECT  3, 3;

DECLARE @param INT = 25;

CREATE TABLE #Results --or table variable
(
     TestDataID INT NOT NULL
    ,ProductID  INT NOT NULL
    ,SalesQty   INT NOT NULL
    ,RowNumber  INT NOT NULL
    ,PRIMARY KEY(RowNumber, ProductID)
);
INSERT  #Results(TestDataID, ProductID, SalesQty, RowNumber)
SELECT  a.TestDataID, a.ProductID, a.SalesQty,
        ROW_NUMBER() OVER(PARTITION BY a.ProductID ORDER BY a.TestDataID) RowNumber
FROM    TestData a;

;WITH CteRecursive
AS
(
    SELECT   a.ProductID
            ,a.RowNumber
            ,a.TestDataID
            ,a.SalesQty
            ,a.SalesQty AS RunningTotal
    FROM    #Results a
    WHERE   a.RowNumber = 1
    AND     a.SalesQty <= @param
    UNION ALL
    SELECT   crt.ProductID
            ,crt.RowNumber
            ,crt.TestDataID
            ,crt.SalesQty 
            ,prev.RunningTotal + crt.SalesQty
    FROM    #Results crt
    INNER JOIN CteRecursive prev ON prev.ProductID = crt.ProductID
    AND     prev.RowNumber + 1 = crt.RowNumber
    WHERE   prev.RunningTotal + crt.SalesQty <= @param
)
SELECT   a.TestDataID
        ,a.ProductID
        ,a.SalesQty

        ,a.RunningTotal
FROM    CteRecursive a
ORDER BY a.ProductID, a.RowNumber;

DROP TABLE #Results;
DROP TABLE TestData;

Results:

TestDataID  ProductID   SalesQty    RunningTotal
----------- ----------- ----------- ------------
2           1           10          10
4           1           10          20
6           1           5           25
10          2           20          20
12          2           2           22