Limit SQL by the sum of the row's value

Question

So I'm kind of stumped here, I have a table setup like this

+-----------+------+
| Timestamp | Size |
+-----------+------+
|   1-1-13  + 10.3 +
+-----------+------+
|   1-3-13  +  6.7 +
+-----------+------+
|   1-5-13  +  3.0 +
+-----------+------+
|   1-9-13  + 11.4 +
+-----------+------+

And I'm wondering if there's any way to run a query like this

SELECT * FROM table ORDER BY timestamp ASC LIMIT BY (SUM(size) <= 20.0);

This should grab the first three rows, because the sum of the size in of the first 3 rows is 20. However, it might not always be 3 rows that equal 20. Sometimes the first row might have a value of 20, and in that case, it should only grab the first one.

I'm already aware that this it's possible to quickly calculate the sum in PHP after the query is run, but I'm trying to accomplish this with just MySQL.

Answer 1

You want to add a running total, and limit based on that, the following should work:

SET @runtot:=0;
 SELECT 
    q1.t,
    q1.s,
    (@runtot := @runtot + q1.s) AS rt
 FROM 
    (SELECT Date AS t,
     SIZE AS s
     FROM  Table1
     ORDER  BY Date
     ) AS q1
WHERE @runtot + q1.s <= 20

Edit: Demo here - SQL Fiddle

Answer 2

 SELECT * FROM ints ORDER BY i;
 +---+
 | i |
 +---+
 | 0 |
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 | 8 |
 | 9 |
 +---+

 SELECT x.* ,SUM(y.i) FROM ints x JOIN ints y ON y.i <= x.i GROUP BY x.i;
 +---+----------+
 | i | SUM(y.i) |
 +---+----------+
 | 0 |        0 |
 | 1 |        1 |
 | 2 |        3 |
 | 3 |        6 |
 | 4 |       10 |
 | 5 |       15 |
 | 6 |       21 |
 | 7 |       28 |
 | 8 |       36 |
 | 9 |       45 |
 +---+----------+

 SELECT x.* ,SUM(y.i) FROM ints x JOIN ints y ON y.i <= x.i GROUP BY x.i HAVING SUM(y.i) <= 20;
 +---+----------+
 | i | SUM(y.i) |
 +---+----------+
 | 0 |        0 |
 | 1 |        1 |
 | 2 |        3 |
 | 3 |        6 |
 | 4 |       10 |
 | 5 |       15 |
 +---+----------+