SQL select top rows based on limit

Question

Please help me t make below select query

Source table

name Amount  
 -----------
 A   2    
 B   3    
 C   2
 D   7

if limit is 5 then result table should be

name Amount  
 -----------
 A   2    
 B   3

if limit is 8 then result table

name Amount  
 -----------
 A   2    
 B   3    
 C   2

Is your limit is based on Total of the amount and not on number of records? — K D, Jan 11 '17 at 14:38
limit is based on total amount, we are using sql server 2014 — Manu, Jan 12 '17 at 05:06

score 4 · Accepted Answer · edited May 23 '17 at 11:45

4

You can use window function to achieve this:

select name,
    amount
from (
    select t.*,
        sum(amount) over (
            order by name
            ) s
    from your_table t
    ) t
where s <= 8;

The analytic function sum will be aggregated row-by-row based on the given order order by name.

Once you found sum till given row using this, you can filter the result using a simple where clause to find rows till which sum of amount is under or equal to the given limit.

More on this topic:

edited May 23 '17 at 11:45

Community

1
1

answered Jan 11 '17 at 14:29

Gurwinder Singh

38,557
6
51
76

1

@JonathanPorter Updated with some explanation. – Gurwinder Singh Jan 11 '17 at 14:42
Much appreciated. – Jonathan Porter Jan 11 '17 at 15:27

SQL select top rows based on limit

1 Answers1