SQL Recursion with row_numbers

Question

I got a temporary view in PostgreSQL which offers the following data (simplified):

temporary_view (sum, rw):

|sum   |  rw|
|1337  |   1|
|42    |   2|
|...   | ...|

Now I need to do a recursion on this until my condition is reached.

For simplicity, let's say I want to calculate, how many rows I need to get a sum > 2000. In my real implementation I need to use a far more complicated formula.

If the sum > 2000 is reached, the recursion should stop and return the last row_number used for the calculation.

How would you implement this in SQL? I tried a lot of things but unfortunately I fail every time.

My thoughts: I would use a temporary view with (sum, row, last_row) where I decrement last_row every time but unfortunately I'm struggling with the implementation ...

Answer 1

select min(rw)
from
(
    select rw, sum(sm) over(order by rw) sm_cum
    from temporary_view
)
where sm_cum > 2000;

Use analytic functions to calculate a cumulative sum then get the min rw. You don't even need a recursion here.

Recursive variant for sum (you can change it on your expression):

with recursive tmp (rw, complicated) as (
    select rw, sm
    from temporary_view where rw = 1 and sm <= 1000
    union 
    select t.rw, tmp.complicated + t.sm
    from temporary_view t join tmp on t.rw = tmp.rw + 1
    where tmp.complicated <= 1000
)
select max(rw) from tmp;

http://sqlfiddle.com/#!12/11583/3

Answer 2

For the original question: to get the whole row, not just the value of rw:

SELECT *
FROM  (
   SELECT *, sum(sm) OVER(ORDER BY rw) sum_sm
   FROM   temporary_view
)
WHERE  sum_sm > 2000  -- you may really want >=
ORDER  BY sum_sm
LIMIT  1;