In MySQL set something similar to a LIMIT based on a percentage

Question

Hi all. I've been using the search function but I think there isn't any related question with the solution for my question (my apologizes if I'm wrong).

Let's imagine we have a simple table in MySQL with this very simple schema: (MATERIAL varchar(10) primary key, QUANTITY smallint). And with rows like: (A,10),(B,8),(C,7),(D,4),(E,1),(F,1),(G,1). So we have 7 materials with a total of 32 items.

What I'd like to get is a SELECT query to get just the 2 first materials (ordered by quantity) but NOT because I know I want exactly 2 but because I know I want the N materials necessary to get a 50% of coverage on quantity.

So, with material A I get 10/32 and that's not enough. With material B I get 18/32 and, as that's more than 50%, that's enough.

In a wrong syntax, just to try to be clearer, I'd like something like:

SELECT material FROM tab ORDER BY quantity DESC LIMIT 50% ON quantity;

Do you have any idea how I could implement this with a single query?

Answer 1

No, but you can emulate it using session variables:

SELECT  material
FROM    (
        SELECT  material, @r := @r + 1 AS rn,
                (
                SELECT  COUNT(*)
                FROM    tab
                ) AS cnt
        FROM    (
                SELECT  @r := 0
                ) vars,
                tab
        ORDER BY
                quantity DESC
        ) q
WHERE   rn < cnt DIV 2