How can I select just some rows in the following table so that they sum up to certain value?
Table
-----
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
2 | 1.5 | 0.0 | 7.5 | 18
3 | 1.0 | 2.0 | 7.5 | 18
4 | 0.0 | 0.5 | 5 | 13
Let's say, the top value I want is 57...
So I need to select the rows from the previous table such that qty1+qty2+qty3+qty4 of each row, get until that 57 value, and discard the other rows. In this example, I would get the following:
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
2 | 1.5 | 0.0 | 7.5 | 18
Because 10+20+1.5+7.5+18 = 57, so I discard rows 3 & 4...
Now I wish that the top value is 50, then I should get:
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
2 | 1.5 | 0.0 | 7.5 | 11
Since these values sum up to 50, and the 7 from row2,qty4 is left out... (BTW the rows are ordered in this particular way because that's the order in which I wish to account for the sums of qtys... It's not valid to sum up first row1, then 3, then 2 then 4, for example... They should always be sumed in the order 1,2,3,4...)
What if I would like the complement of this? I mean, the other two rows I didn't got in the last result.
First case:
id | qty1 | qty2 | qty3 | qty4
------------------------------
3 | 1.0 | 2.0 | 7.5 | 18
4 | 0.0 | 0.5 | 5 | 13
Second case:
id | qty1 | qty2 | qty3 | qty4
------------------------------
2 | 0.0 | 0.0 | 0.0 | 7
3 | 1.0 | 2.0 | 7.5 | 18
4 | 0.0 | 0.5 | 5 | 13
(If the second case is too complicated, how about obtaining:
id | qty1 | qty2 | qty3 | qty4
------------------------------
1 | 0.0 | 0.0 | 10 | 20
Because ad