MySQL ROW_NUMBER for multiple columns

Question

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.

Can you elaborate more on "top-ten for salary, but the bottom-five for grievances." with example data or output required — geeksal, Apr 22 '16 at 17:12
You might want to provide sample data and desired results, or even the SQL Server query. — Gordon Linoff, Apr 22 '16 at 17:15
I thought it was pretty self-explanatory. Grab all employees whose salary is among the ten highest salaries AND whose grievance count is among the five lowest grievance counts. — Steve D, Apr 22 '16 at 20:21

score 1 · Accepted Answer · answered Apr 22 '16 at 17:16

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;

MySQL ROW_NUMBER for multiple columns

1 Answers1