patternsqlMinor

get max serial no. according to sum of its corresponding amount

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

amountserialitsgetmaxsumaccordingcorresponding

Problem

I have a table with data as follows:

s_no  amount
1      500
2      500
3      500
4      500
5      500

I need a query to find the max(s_no) according to the sum(amount)
For e.g. when sum(amount)<=1600 i need 4 as result.I can get the desired output by using cursor but it would be easier if i could use query.. I tried a query as follows:

SELECT MAX(s_no) from table HAVING SUM(amount)<=1600

But it doesn't work. I would appreciate any kind of help.
Thank you.

Solution

Another way – that works in versions prior to 2012 – is to use a recursive CTE.

Tested at rextester.com:

WITH ct AS
  ( SELECT TOP (1) s_no, amount, running_total = amount
    FROM tableX
    ORDER BY s_no

    UNION ALL

    SELECT n.s_no, n.amount, n.amount + n.previous_total
    FROM 
      ( SELECT t.s_no, t.amount, previous_total = ct.running_total,
               rn = ROW_NUMBER() OVER (ORDER BY t.s_no) 
        FROM ct
          JOIN tableX AS t
            ON t.s_no > ct.s_no
        WHERE ct.running_total = 1600 
OPTION (MAXRECURSION 0) ;          -- no limit on recursion

The CTE's anchor (the UNION ALL's first leg) selects only the first row. Then in each iteration the CTE's recursive part (the second leg) selects the next row and computes the running total until the sum is 1600 or more.

The ROW_NUMBER() trick and subquery is to overcome the limitation that TOP is not allowed in the recursive parts of CTEs.

Code Snippets

WITH ct AS
  ( SELECT TOP (1) s_no, amount, running_total = amount
    FROM tableX
    ORDER BY s_no

    UNION ALL

    SELECT n.s_no, n.amount, n.amount + n.previous_total
    FROM 
      ( SELECT t.s_no, t.amount, previous_total = ct.running_total,
               rn = ROW_NUMBER() OVER (ORDER BY t.s_no) 
        FROM ct
          JOIN tableX AS t
            ON t.s_no > ct.s_no
        WHERE ct.running_total < 1600 
      ) AS n
    WHERE n.rn = 1
  )
SELECT *
FROM ct 
WHERE running_total >= 1600 
OPTION (MAXRECURSION 0) ;          -- no limit on recursion

Context

StackExchange Database Administrators Q#159115, answer score: 5

Revisions (0)

No revisions yet.