mysql - Select minimal count of rows with total sum greater than or equal to a given threshold

Question

I have an sql table trade with following data

id| value | price
 1| 0.1   |1
 2| 0.5   |2
 3| 0.9   |2
 4| 0.3   |2

How do I make an SQL query so that I get the count of entries limited to total value of 0.9 for price 2 ascending by id . For example:-

Select Count of id FROM trade WHERE sum(value) <= 0.9 and price = '2'

Result should come as

 3

As there are total 3 entries with id 2,3,4 with values 0.5,0.9,0.3 with price 2 . The sum of them is 1.7 which is more than 0.9 but if we take 0.5 and 0.3 it combines to 0.8 which is lesser than 0.9 . So result should be 3 as it consists value of atleast 0.9 with price 2.
Also is there way to get the id of the results with last and first highest value in order.

So it looks like :-

4
2
3

Help appreciated :)

Answer 1

select id from 
    (select id, if(not(@sum > 0.9), 1, 0) mark,  (@sum:=@sum+value) as sum 
        from trade cross join  (select @sum:=0) s  
        where price=2 order by value asc) t 
where mark =1 

The inner query counts cumulative sum and addional field mark, which is equal one while sum is less and turn into zero when it is over 0.9. Since it's working one step later, it gathers the first row where sum is above the limit.

The result of the inner select

id   mark   sum
4    1      0.30000001192092896
2    1      0.800000011920929
3    1      1.699999988079071

Now in the outer query you just need to select rows with mark equal 1. And it results in 4,2,3

demo on sqlfiddle