How do I limit a select by a sum?

limit, postgresql, sql, sum

Solution

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.

Problem

I want to select all the cheapest toys of my stock, amounting a total of 10.0 USD: That is, I want to do something that looks like this: `select * from toy where sum(price) < 10.0 order by price;` What would be the correct SQL? To make it clearer, I'll add an example. Suppose I have these items in my table: ``` name | price ------------------+------- car | 1 boat | 2 telephone | 8 gold bar | 50 ``` The result I would be: 1 car and 1 boat. Totaling the price of 3 USD. I cannot select the telephone because it would amount 13 USD, and that is larger than 10. Any Ideas?

Original source