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

Answer 2

Although it may not be the most efficent way (as you're still, in essence, selecting everything first), I'd look at using a running total.

Something like:

SELECT
  *
FROM
  (
  SELECT
    id
    , date
    , amount
    , (SELECT SUM(amount) FROM Transactions WHERE id <= t.id) AS RunningTotal
  FROM
    Transactions t
) t1
WHERE
  t1.RunningTotal < 6