sql - Postgres: limit by the results of a sum function -

create table inventory_box (  box_id varchar(10),  value   integer );  insert inventory_box values ('1', 10), ('2', 15), ('3', 20); 

i prepared sql fiddle schema.

i select list of inventory boxes combined value of above 20

possible result 1. box 1 + box 2 (10 + 15 >= 20)  

here doing right now:

select * inventory_box limit 1 offset 0; -- count on client side , see if got enough -- got 10 select * inventory_box limit 1 offset 1; -- count on client side , see if got enough -- got 15, add first query returned 10 -- total 25, ok, got enough, return answer 

i looking solution scan stop reaches target value

your question update clarifies actual requirements simpler full-blown "subset sum problem" suspected @ghostgambler:
fetch rows until sum big enough.

i sorting box_id deterministic results. might drop order by altogether any valid result bit faster, yet.

slow: recursive cte

with recursive (    select *, row_number() on (order box_id) rn      inventory_box    ) , r (    select box_id, val, val total, 2 rn          rn = 1     union    select i.box_id, i.val, r.total + i.val, r.rn + 1      r    join   using (rn)     r.total < 20    ) select box_id, val, total   r order  box_id; 

fast: pl/pgsql function loop

using sum() window aggregate function (cheapest way).

create or replace function f_shop_for(_total int)   returns table (box_id text, val int, total int) $func$ begin  total := 0;  box_id, val, total in     select i.box_id, i.val           , sum(i.val) on (order i.box_id) total     inventory_box loop     return next;     exit when total >= _total; end loop;   end $func$ language plpgsql stable;  select * f_shop_for(35); 

i tested both big table of 1 million rows. function reads necessary rows index , table. cte slow, seems scan whole table ...

sql fiddle both.

aside: sorting varchar column (box_id) containing numeric data yields dubious results. maybe should numeric type, really?