Limit by a cumulative column amount

Question

Say I have a table with 3 columns: id, row_type, row_score

I would like to select the first (or latest) rows, but limit the selection based on the cumulative score of the fetched ids

the example table

id | row_type | row_score
 1          a                  1
 2          a                  1
 3          b                  2
 4          c                  3
 5          a                  1
 6          b                  2
 7          a                  1
...

the result of 1st row, with a limit of a cumulative score of 4:

id | row_type | row_score
 1          a                  1
 2          a                  1
 3          b                  2

Answer 1

This query should do what you want. It uses a variable to keep a cumulative score, then uses that in a HAVING clause to restrict the rows returned:

SELECT t1.*, @cum_score := @cum_score + row_score AS cum_score
FROM table1 t1
JOIN (SELECT @cum_score := 0) c
HAVING cum_score <= 4
ORDER BY cum_score

Output:

id  row_type    row_score   cum_score
1   a           1           1
2   a           1           2
3   b           2           4

SQLFiddle Demo