How to select the top 3 salaries of the department?

Question

I'm trying to solve a SQL problem online that has 2 tables as following,

Employee table

The Employee table holds all employees.

+----+-------+--------+--------------+
| Id | Name  | Sa1ary | DepartmentId |
+----+-------+--------+--------------+
|  1 | Joe   |  70000 |            1 |
|  2 | Henry |  80000 |            2 |
|  3 | Sam   |  60000 |            2 |
|  4 | Max   |  90000 |            1 |
|  5 | Janet |  69000 |            1 |
|  6 | Randy |  85000 |            1 |
+----+-------+--------+--------------+

Department table

The SQL should return the following data

I have the SQL queries as following,

    SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary 
FROM Employee E INNER JOIN Department D ON E.DepartmentId = D.Id 
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee 
       WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3
ORDER by E.DepartmentId, E.Salary DESC;

The SQL is good, but, I'm little confused about the < 3 part. Shouldn't it be = 3 as in the question it was asked about top 3 salaries ? I appreciate an explanation of the SQL and will be helpful.

Answer 1

I was working on the same SQL problem.

Just in case someone may need help.

Here's the answer I came up with.

SELECT
    dpt.Name AS Department,
    e1.Name AS Employee,
    e1.Salary AS Salary
FROM Employee AS e1
INNER JOIN Department dpt
ON e1.DepartmentID = dpt.Id
WHERE 3 > (
           SELECT COUNT(DISTINCT Salary)
           FROM Employee AS e2
           WHERE e2.Salary > e1.Salary
           AND e1.DepartmentID = e2.DepartmentID
          )
ORDER BY
Department ASC,
Salary DESC;

1. The hard part is to get the top 3 salaries of each department. I first count the [number of employees with a higher salary].

   After that, I use 3 > [number of employees with a higher salary] to keep the top 3 salaries only. (If there are more than 3 employees in top 3, which is to say some of them have the same salary, all of them will be included.)

   Query

   SELECT *
   FROM Employee e1
   WHERE 3 > (
              SELECT COUNT(DISTINCT Salary)
              FROM Employee e2
              WHERE e2.Salary > e1.Salary
              AND e1.DepartmentID = e2.DepartmentID
             );
   

   Output

   +------+-------+--------+--------------+
   | Id   | Name  | Salary | DepartmentId |
   +------+-------+--------+--------------+
   |    1 | Joe   |  70000 |            1 |
   |    2 | Henry |  80000 |            2 |
   |    3 | Sam   |  60000 |            2 |
   |    4 | Max   |  90000 |            1 |
   |    6 | Randy |  85000 |            1 |
   +------+-------+--------+--------------+
   

2. Then it's the easy part. You can just join this table with Department on DepartmentID to get the department name.

   Final Output

   +------------+----------+--------+
   | Department | Employee | Salary |
   +------------+----------+--------+
   | IT         | Max      |  90000 |
   | IT         | Randy    |  85000 |
   | IT         | Joe      |  70000 |
   | Sales      | Henry    |  80000 |
   | Sales      | Sam      |  60000 |
   +------------+----------+--------+
   

Answer 2

with sal_CTE as
(
 select e.Id as emp_id,
    d.Name as dept_name,
    e.Name as emp_name,
    e.Salary as emp_sal,
    DENSE_RANK() OVER(PARTITION BY d.ID Order By  e.Salary desc) as rank
 from Employee e inner join department d
 ON (e.DepartmentId = d.Id)
)

select dept_name as Department,
emp_name as Employee,
emp_sal as Salary from sal_CTE
where rank <=3
order by dept_name;

Answer 3

You can achieve this by windowing function works with mysql>5.7 and all major databases supporting windowing function

select  dept_name, emp_name, salary from (
select d.name as dept_name, e.name as emp_name, e.salary, 
rank() over(partition by d.name order by e.salary desc) as rank_count 
from employee e, department d 
where d.id = e.dept_id) a where a.rank_count <4

You can change the inner join to left join if required when one table has a null key and the other one does not have the mapping key

Answer 4

Another way of doing it:

WITH table1 AS 
(
    SELECT Department.Name AS Department, Employee.Name AS Employee, Salary
    FROM Employee
    INNER JOIN Department 
    ON DepartmentId = Department.Id
),

table2 AS 
(
    SELECT * FROM ( 
                    SELECT Department, Employee, Salary, DENSE_RANK() OVER                       (PARTITION BY Department ORDER BY Salary DESC) AS rank 
                    FROM table1
                   ) t
    WHERE rank<=3 
)

SELECT Department, Employee, Salary 
FROM table2;

Answer 5

  SELECT 
  Department, 
  Employee, 
  Salary
  FROM 
  (

  SELECT 
  D.Name AS Department, 
  E.Name AS Employee, 
  E.Salary AS Salary,
  DENSE_RANK() OVER (
  PARTITION BY d.NAME
  ORDER BY d.name, E.SALARY DESC
  ) AS RANK

  FROM Employee E 
  INNER JOIN Department D 
  ON E.DepartmentId = D.Id
  group by d.name, e.name, e.salary
  ) 
  WHERE RANK <= 3

FOR ORACLE 11G

• Above query is using dense rank function.
• Department Top Three Salaries, solutions accepted on leetcode https://leetcode.com/problems/department-top-three-salaries/

Answer 6

dense_rank() works,rank() creates a gap if you have 2 rows with the same salary.

with  temp_tbl as (
select d.name as Department,i.name as Employee,Salary,dense_rank() over (partition by departmentid order by salary desc) as rnk
from employee i,department d
where i.departmentid=d.id)
select  Department,Employee,Salary
from temp_tbl
where rnk<4

Answer 7

WITH tbl
     AS (SELECT NAME,
                salary,
                departmentid,
                Dense_rank()
                  OVER (
                    partition BY departmentid
                    ORDER BY salary DESC) AS nums
         FROM   employee)
SELECT d.NAME   Department,
       tbl.NAME Employee,
       tbl.salary
FROM   tbl
       INNER JOIN department d
               ON tbl.departmentid = d.id
WHERE  tbl.nums <= 3 

Answer 8

With CTE_EMP AS 
(
Select d.Name AS Department, e.Name as Employee, e.Salary,
 ROW_NUMBER() over (partition by DepartmentId order by Salary desc) AS RowNumber
From Employee e
JOIN Department d on d.Id = e.DepartmentId
)
Select Department, Employee, Salary FROM CTE_EMP Where RowNumber <= 3

Answer 9

select r.name as Department , t.name as Employee , t.salary from Department as r 
inner join
(select b.dept_id as departmentID , b.name as name , b.salary as salary from
(select a.departmentId as dept_id , a.name as name , a.salary as salary , dense_rank() over (partition by a.departmentId order by a.salary DESC) as
dense_rank' from (select departmentId , name , salary from Employee 
order by departmentID ASC , salary DESC) as a) as b
where b.dense_rank <= 3) as t
on r.id = t.departmentId