Select from table

Question

select records from table using like for loop in postgres where user given value less than particular column value then stop loop else select next record based on remaining value

ex:

NO:   value:   
inv1 5
inv2 20
inv3 30

user given value 23 means

No: value: selectedvalue

inv1  5      5
inv2  20     18

Answer 1

You are looking for a cumulative sum and some additional logic:

select t.*,
       (case when cume_value < 23 then value
             else cume_value - value
        end)
from (select t.*,
             sum(value) over (order by ??) as cume_value
      from t
     ) t
where cume_value < 23;

The ?? represents the column you are using for ordering the rows in the table. SQL tables represent unordered sets so you need an ordering column. In your case, this might be value.

Answer 2

with temp as (

SELECT value,number1 FROM (SELECT number1, sum(value) OVER
(ORDER BY number1 ) t FROM table as ta

) a where 8+value > t )

select value,number1 from temp