get max serial no. according to sum of its corresponding amount 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)&lt;=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)&lt;=1600


But it doesn't work. I would appreciate any kind of help.
Thank you.
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 &gt; ct.s_no
        WHERE ct.running_total &lt; 1600 
      ) AS n
    WHERE n.rn = 1
  )
SELECT *
FROM ct 
WHERE running_total &gt;= 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.