How to select rows with a particular rank per partition?

Question

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            |
+----+-------+--------+--------------+

2. 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?

Answer 1

Window functions have some limitation for example you cannot 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 in the SELECT list (between the two keywords SELECT and FROM) at the same place where usual functions, expressions 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 FROM. Then you can have the salary_rank = 1 condition in the WHERE clause of the main query.

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 
      ) temp
WHERE salary_rank=1;