Mysql – How to select rows with a particular rank per partition

MySQLpartitioningwindow functions

I want to pick the row with the highest value in a column based on partitioning on another column. The problem is to pick the highest-earning employees from each department, click here for the complete problem.

The tables are:

  1. Employee table:
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
  1. The Department table:
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

The objective is to pick employees who have the highest salary in each of the departments.

My query:

select department, employee, salary 
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank 
      from employee e 
      join department d on e.departmentid=d.id 
      group by d.id 
      having salary_rank=1) temp;

Error:

You cannot use the alias 'salary_rank' of an expression containing a window function in this context.'

How can I use a particular rank in the having clause in order to get the highest ranking employee in each department?

I am using the where clause that works:

select department, employee, salary 
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank 
      from employee e 
      join department d on e.departmentid=d.id) temp 
where salary_rank=1;

but I wanted to use the having clause.

I have tried reading the documentation and watched a couple of tutorials, all of them use the having clause for ranking per partition. What am I missing here?

Best Answer

window functions have some limitation for example you can use them in having.

You can only use window functions in the SELECT list and ORDER BY clauses of a query.

Window functions are listed between the two keywords SELECT and FROM at the same place where usual functions and columns are listed. They contain the keyword OVER.

The definition :

-- Window functions appear between the key words SELECT and FROM
SELECT   ...,
         <window_function>,
         ...
FROM     <tablename>
...
;

And in mysql WINDOW functions restriction is written

Using such functions in a subquery of these statements (to select rows) is permitted.

So your approach is correct. You Have to put the SELECT in a subquery or as Akina said you can use cte , which is only another FORM

Your query has to be

select department, employee, salary 
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank 
      from employee e 
      join department d on e.departmentid=d.id 
      group by d.id ) temp
WHERE salary_rank=1;
Related Question