CODEWITHSUNDEEP
mysql
spinsch
spinsch

Reputation: 1415

Get as much as possible rows until a given minimum amount is achieved in MySQL

For example we have the following Query:

SELECT
    item_id,
    order_id,
    product_id,
    qty
FROM
    orders
WHERE
    product_id = 18253
ORDER BY 
    item_id ASC;

... and that's the result of it:

+---------+----------+------------+------+
| item_id | order_id | product_id | qty  |
+---------+----------+------------+------+
|   15329 |     7369 |      18253 |    3 |
|   15330 |     7370 |      18253 |    1 |
|   15331 |     7371 |      18253 |    7 |
|   15332 |     7372 |      18253 |    1 |
|   15333 |     7373 |      18253 |    1 |
|   15334 |     7377 |      18253 |    1 |
|   15336 |     7379 |      18253 |    2 |
|   15337 |     7380 |      18253 |    1 |
|   15340 |     7383 |      18253 |    1 |
|   15341 |     7384 |      18253 |    1 |
+---------+----------+------------+------+
10 rows in set (0,04 sec)

Now I want to get all possible rows (as much as possible) until the sum of qty is at least five. Five should be the minimum quantity. Sorted by ascending (item_id).

+---------+----------+------------+------+
| item_id | order_id | product_id | qty  |
+---------+----------+------------+------+
|   15329 |     7369 |      18253 |    3 |  // 3
|   15330 |     7370 |      18253 |    1 |  // 4
|   15331 |     7371 |      18253 |    7 |  // 11
+---------+----------+------------+------+

So the sum of qty above is 3, 4, 11. The third row makes the sum 5 or above so that's all I need.

Another example when I have a quantity of 13 in my inventory:

+---------+----------+------------+------+
| item_id | order_id | product_id | qty  |
+---------+----------+------------+------+
|   15329 |     7369 |      18253 |    3 | // 3
|   15330 |     7370 |      18253 |    1 | // 4
|   15331 |     7371 |      18253 |    7 | // 11
|   15332 |     7372 |      18253 |    1 | // 12
|   15333 |     7373 |      18253 |    1 | // 13
+---------+----------+------------+------+

How can I do that, is there a possibility?

thanks :)

Upvotes: 0

Views: 43

Answers (2)

jkettler
jkettler

Reputation: 19

Unless I'm missing something, this can be done by adding the limit clause to your query, like:

SELECT
    item_id,
    order_id,
    product_id,
    qty
FROM
    orders
WHERE
    product_id = 18253
ORDER BY 
    item_id ASC;
LIMIT 5;

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39487

You can generate cummulative sum of quantity in the order of increasing item_id and then filter out the result as needed:

select *
from (
    select item_id,
        order_id,
        product_id,
        qty,
        @cum_qty := @cum_qty + qty as cum_qty
    from orders, (select @cum_qty := 0) t
    where product_id = 18253
    order by item_id
    ) t
where cum_qty - qty < 5;  -- or 13 or whatever

Demo

Upvotes: 2

Related Questions