sql - Postgresql select until certain total amount is reached

Question

I was wondering if I could get any help with the following problem.

I have a table of transactions (simplified below) and I only want to select transactions until my amount total reaches a certain amount.

Transactions table

 id |   date   | amount 
----|----------|--------
 1  | 1/1/2012 |   2 
 2  | 2/1/2012 |   3 
 3  | 3/1/2012 |   4
 4  | 4/1/2012 |   20 
 5  | 5/1/2012 |   1 
 6  | 6/1/2012 |   2

Now say I want to do a select on the table until the amount total is 6 i.e just the first 2 rows, how would I do this?

I was thinking of maybe doing a join with itself and some sum but not really getting anywhere. I'd prefer no to use any functions if possible.

Also anything similar for minimum amount.

Any help would be much appreciated :)

T

Answer 1

select id, 
       date, 
       amount, 
       running_total
from (
    select id,
           date,
           amount,
           sum(amount) over (order by date asc) as running_total
    from transactions
) t
where running_total <= 6