185. Department Top Three Salaries


The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | 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            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

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

b'
\n
\n\n
\n

Solution

\n
\n

Approach: Using JOIN and sub-query [Accepted]

\n

Algorithm

\n

A top 3 salary in this company means there is no more than 3 salary bigger than itself in the company.

\n
select e1.Name as \'Employee\', e1.Salary\nfrom Employee e1\nwhere 3 >\n(\n    select count(distinct e2.Salary)\n    from Employee e2\n    where e2.Salary > e1.Salary\n)\n;\n
\n

In this code, we count the salary number of which is bigger than e1.Salary. So the output is as below for the sample data.

\n
| Employee | Salary |\n|----------|--------|\n| Henry    | 80000  |\n| Max      | 90000  |\n| Randy    | 85000  |\n
\n

Then, we need to join the Employee table with Department in order to retrieve the department information.

\n

MySQL

\n
SELECT\n    d.Name AS \'Department\', e1.Name AS \'Employee\', e1.Salary\nFROM\n    Employee e1\n        JOIN\n    Department d ON e1.DepartmentId = d.Id\nWHERE\n    3 > (SELECT\n            COUNT(DISTINCT e2.Salary)\n        FROM\n            Employee e2\n        WHERE\n            e2.Salary > e1.Salary\n                AND e1.DepartmentId = e2.DepartmentId\n        )\n;\n
\n
| Department | Employee | Salary |\n|------------|----------|--------|\n| IT         | Joe      | 70000  |\n| Sales      | Henry    | 80000  |\n| Sales      | Sam      | 60000  |\n| IT         | Max      | 90000  |\n| IT         | Randy    | 85000  |\n
\n
'