Replacing LIMIT static value with column value

Question

I have a table that looks like this:

ID     Damage
1      10
2      7
3      153587
4      1
...1M more rows

I have another table that has a column that represents the percentile in the amount of rows I need to grab, so if its top 10 percentile the column value will be 100000 I want to grab based on damage.

Is there a way instead of saying LIMIT 100000, since the percentile changes to replace 100000 with essentially a variable or the column value?

Second table:

Days    Percentile_Affected    Damage_Sum
14      87000
30      161000
90      371000
...

Answer 1

If the ids had no gaps, you could just use the id. Instead, you can add a counting variable and use that:

select t.*
from (select t.*, (@rn := @rn + 1) as rn
      from (select t.*
            from t
            order by id
           ) t cross join
           (select @rn := 0) params
     ) t
where rn < (select "a column" from "another table");

The alternative is to construct the query and use dynamic SQL:

select @sql := replace('select t.* from t limit [limit]', [limit], "a column")
from "another table";

prepare stmt from @sql;
execute stmt;

Or, use a placeholder for the limit:

set @sql = 'select t.* from t limit ?';
select @limit := "a column"
from "another table";

prepare stmt from @sql;
execute stmt using @limit;