sql - Select rows until a total amount is met in a column (mysql)

Question

I have seen this issue in SF, but me being a noob I just can't get my fried brain around them. So please forgive me if this feels like repetition.

My Sample Table

--------------------------
ID   | Supplier   | QTY
--------------------------
1       1          2
2       1          2
3       2          5
4       3          2
5       1          3
6       2          4

I need to get the rows "UNTIL" the cumulative total for "QTY" is equal or greater than 5 in descending order for a particular supplier id.

In this example, for supplier 1, it will be rows with the ids of 5 and 2.

    Id - unique primary key
    Supplier - foreign key, there is another table for supplier info.
    Qty - double

Answer 1

It ain't pretty, but I think this does it and maybe it can be the basis of something less cumbersome. Note that I use a "fake" INNER JOIN just to get some variable initialized for the first time--it serves no other role.

SELECT ID,
       supplier,
       qty,
       cumulative_qty
FROM
(
    SELECT
        ID,
        supplier,
        qty,
        -- next line keeps a running total quantity by supplier id
        @cumulative_quantity := if (@sup <> supplier, qty, @cumulative_quantity + qty) as cumulative_qty,
        -- next is 0 for running total < 5 by supplier, 1 the first time >= 5, and ++ after
        @reached_five := if (@cumulative_quantity < 5, 0, if (@sup <> supplier, 1, @reached_five + 1)) as reached_five,
        -- next takes note of changes in supplier being processed
        @sup := if(@sup <> supplier, supplier, @sup) as sup
    FROM
    (
        --this subquery is key for getting things in supplier order, by descending id
        SELECT *
        FROM `sample_table`
        ORDER BY supplier, ID DESC
     ) reverse_order_by_id
    INNER JOIN
    (
        -- initialize the variables used to their first ever values
        SELECT @cumulative_quantity := 0, @sup := 0, @reached_five := 0
    ) only_here_to_initialize_variables
) t_alias
where reached_five <= 1 -- only get things up through the time we first get to 5 or above.