CODEWITHSUNDEEP
mysqlsqldatabaserow-number
Steve D
Steve D

Reputation: 393

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.

Upvotes: 0

Views: 761

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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;

Upvotes: 1

Related Questions