Question 112862 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL to find the highest three unique salaries for each department

Please let me know how to get this output, I have searched a lot but all of the queries shows two separate tables one for employees and another department. Here I do not have it so I tried this:

SELECT e.department_id,  e.Salary  
FROM Employees1 e INNER JOIN employees1 s 
ON e.Department_Id = s.Department_Id
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employees1 
WHERE e.Department_Id = s.Department_Id AND s.Salary > E.Salary) < 3
ORDER by E.Department_Id, E.Salary ;

But the output is not right.

Tags:

2

Answers


  1. 0

    If you are using MySql 8.0 or newer, then we can use the window function rank() since we need to find the unique salary value.

    Query

    with cte as(
  select *, rank() over(
    partition by department_id
    order by salary desc
  ) as rn
  from (
    select distinct department_id, salary
    from employees
  ) as t
)
select department_id, salary
from cte
where rn <= 3;
    Login or Signup to reply.
  2. 0

    You gonna need window function for sure:

    SELECT department_id, salary FROM (
    SELECT DISTINCT
        department_id, 
        salary,
        DENSE_RANK() OVER( PARTITION BY department_id ORDER BY salary DESC ) as `rank`
    FROM `employees1` 
) ranked
WHERE `rank` <= 3
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search