Search code examples
mysqlsqldatabaserow-number

MySQL ROW_NUMBER for multiple columns

I have a table that looks like:

+-------------+--------+------------+
| Employee ID | Salary | Grievances |
+-------------+--------+------------+
| 101         | 70,000 | 12         |
| 102         | 90,000 | 100        |
| ...         | ...    | ...        |
+-------------+--------+------------+

And I want to find all employees who are in the top-ten for salary, but the bottom-five for grievances. I (think I) know how to do this in SQL Server using ROW_NUMBER, but how to do it in MySQL? I've seen the goto question on doing this, but it doesn't really apply to a multiple column ordering.

Solution

  • If I understand correctly, you can do this with a self-join:

    select s.*
from (select t.*
      from t
      order by salary desc
      limit 10
     ) s join
     (select t.*
      from t
      order by grievances asc
      limit 5
     ) g
     on s.employeeid = g.employeeid;