SQL to find the highest three unique salaries for each department-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

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