0

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?

1
  • 1
    How can I use a particular rank in the having clause in order to get the highest ranking employee in each department? Noway. Window function is calculated over final rowset, after HAVING is applied. PS. If you may use window function then you may use CTE - move your subquery to it.
    – Akina
    Oct 6 '20 at 4:52
0

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;
2
  • I did not get your answer. Can you write the final query? Oct 7 '20 at 2:41
  • sure i added the query
    – nbk
    Oct 7 '20 at 2:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.