How to limit the selection in SQL Server by sum of a column?

Question

Can I limit rows by sum of a column in a SQL Server database?

For example:

Type | Time (in minutes)
-------------------------
A    | 50
B    | 10
C    | 30
D    | 20
E    | 70
...

And I want to limit the selection by sum of time. For example maximum of 100 minutes. Table must look like this:

Type | Time (in minutes)
-------------------------
A    | 50
B    | 10
C    | 30

Any ideas? Thanks.

score 2 · Accepted Answer · answered Sep 21 '11 at 09:53

DECLARE @T TABLE
(
[Type] CHAR(1) PRIMARY KEY,
[Time] INT
)
INSERT INTO @T
SELECT 'A',50 UNION ALL
SELECT 'B',10 UNION ALL
SELECT 'C',30 UNION ALL
SELECT 'D',20 UNION ALL
SELECT 'E',70;


WITH    RecursiveCTE
AS      (
        SELECT TOP 1 [Type], [Time], CAST([Time] AS BIGINT) AS Total
        FROM @T
        ORDER BY [Type]
        UNION   ALL
        SELECT  R.[Type], R.[Time], R.Total
        FROM    (
                SELECT  T.*,
                        T.[Time] + Total AS Total,
                        rn = ROW_NUMBER() OVER (ORDER BY T.[Type])
                FROM    @T T
                JOIN    RecursiveCTE R
                        ON  R.[Type] < T.[Type]
                ) R
        WHERE   R.rn = 1 AND Total <= 100
        )
SELECT  [Type], [Time], Total
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

Or if your table is small

SELECT t1.[Type],
       t1.[Time],
       SUM(t2.[Time])
FROM @T t1
 JOIN @T t2
ON t2.[Type] <= t1.[Type]
GROUP BY t1.[Type],t1.[Time]
HAVING SUM(t2.[Time]) <=100

Thanks for the solution, it is nice but I want to know if this solution will be slow with large number of rows..? — EvanBlack, Sep 21 '11 at 10:00
@EvanBlack - Yes both are quite inefficient with large numbers of rows but the CTE one will scale better assuming you have the correct indexes to allow the recursive part to seek in and find the next row. It also won't end up processing **all** the rows just to return a subset as the join query would. [My answer here](http://stackoverflow.com/questions/7357516/subquery-or-leftjoin-with-group-by-which-one-is-faster/7454564#7454564) has some performance stats or google "running totals SQL Server" to find plenty of discussion. — Martin Smith, Sep 21 '11 at 10:16

How to limit the selection in SQL Server by sum of a column?

1 Answers1