Department Top Three Salaries

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 | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
±—±------±-------±-------------+
The Department table holds all departments of the company.
±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+

Example

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 (order of rows does not matter).
±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
±-----------±---------±-------+

Solution

@An employee's salary is in top three, if only if there are less than 3 salaries which are higher.
# Write your MySQL query statement below
SELECT D.Name Department, E.Name Employee, E.Salary Salary FROM Department D
  JOIN Employee E
    ON E.DepartmentId=D.Id
 WHERE (SELECT COUNT(DISTINCT E1.Salary) FROM Employee E1 WHERE E1.DepartmentId=D.Id 
        AND E1.Salary>E.Salary)<3
 ORDER BY Department,Salary DESC