Select all records in which the sum of values are equal to a specific value

I have a products table that looks like this (the other fields are hidden for simplicity)

id | price
----------
1  |  199
2  |  50
3  |  320
4  |  120

I'm trying to create a query that selects all products in which values are equal to or less than a specific value, for example:

A customer wants to spend $600 at max, the first 3 products of the example should be returned (199+50+320 = 569)

I tried with the query below but got no success

select * from products group by id having sum(price) <= 600

I'm trying to create a query that selects all products in which values
are equal to or less than a specific value

We need to create a cumulative sum for this case.

select id,
       price
from ( SELECT id,
              price,
              SUM(price) OVER(ORDER BY id asc ) AS cumulative_sum
       FROM products
     ) cum_sum
where cumulative_sum <=600;

https://dbfiddle.uk/8A46Gmcy

Note, above cumulative sum is ordered by the id which means it will test the first products.

If id 1 is equal to 601 query will return empty set.

You can change the order of the cumulative SUM based on your needs.

SUM(price) OVER(ORDER BY price asc ) AS cumulative_sum  ---will start from the lowest price to max

Edit A more suitable answer/question would be finding every possible combination of the values which the sum is less than 600.

with recursive cte as ( 
    select id as max_id,
           price,
           cast(id as char(255))  as possible_combination 
           from products
  union all
    select p.id as max_id,
           c.price + p.price as price,
           cast(concat(c.possible_combination ,',' , p.id) as char(255))  as possible_combination
   from cte c
   inner join products p on p.id > c.max_id                       
)
select possible_combination,price
from cte
where price <= 600 ;

https://dbfiddle.uk/IaqXBqzi

StackExchange Database Administrators Q#321376, answer score: 5