How to limit the selection to the amount?

Question

I want to select all the cheapest toys in my inventory, totaling $ 10.0:

That is, I want to do something similar to this:

select * from toy where sum(price) < 10.0 order by price;

What will be the correct SQL?

To make it clearer, I will add an example. Suppose I have these elements in my table:

  name | price ------------------+------- car | 1 boat | 2 telephone | 8 gold bar | 50 

As a result, I would: 1 car and 1 boat.

Total cost 3 USD. I can’t choose a phone because it will be $ 13, and that’s over 10.

Any ideas?

Answer 1

Try:

 SELECT a.name, max(a.price) price FROM Toy a JOIN Toy b on a.price > b.price or (a.price=b.price and a.name>=b.name) GROUP BY a.name HAVING SUM(b.price) <= 10.0 order by 2 

SQLFiddle here .

Answer 2

SQL Fiddle

 select name, price, total from ( select name, price, sum(price) over( order by price rows between unbounded preceding and current row ) total from toy ) s where total <= 10 order by price 

Note that while between unbounded preceding and current row is the default frame, the default mode is range . Thus, it is necessary that at least rows unbounded preceding declared as the current row is the end of the frame by default.

Answer 3

Here is an implementation using recursive CTE. There are other solutions for this; you can use Google for "running totals."

 WITH RECURSIVE CTE_RN AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Price) RN FROM Toys ) , CTE_Rec AS ( SELECT name, price, rn FROM CTE_RN WHERE RN = 1 UNION ALL SELECt r.name, a.price + r.price as price, r.rn FROM CTE_RN r INNER JOIN CTE_Rec a on a.RN + 1 = r.RN where a.price+r.price <= 10 ) SELECT name, price as total_price FROM CTE_Rec 

SQLFiddle Demo

PS: It is highly dependent on the RDBMS, so it was important to include this information at the beginning.