Extracting top 10 percent from 100 different tables using sql

Question

I have a database which contains 100 tables with distinct employee_ID and Salary. the total rows in the database is 100 million (sum of all the rows in 100 tables). The tables don't have equal number of rows. let's say the 100 tables are according to regions. I want to return top 10 % of the salaries from all the tables. How can it be done?

Answer 1

First, having multiple tables with the same formats is a sign of bad database design, in general. It is better to have one table with all the rows.

Second, a hundred tables is starting to get into the limits of what MySQL queries can handle. The following will not have great performance because it requires sorting all the data, but it should work:

select es.*
from (select es.*, (@rn := @rn + 1) as rn
      from ((select employee_id, salary from table001) union all
            . . .
            (select employee_id, salary, from table100)
           ) es cross join
           (select @rn := 0) vars
      order by salary desc
     ) t
where rn <= @rn * 0.1;

This works because after the subquery has enumerated the rows, the variable @rn contains the total number of rows. This can be used for the final filter.