185. Department Top Three Salaries - Detailed Explanation

Problem Statement

Table Employee has the following schema:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| Id            | int     |
| Name          | varchar |
| Salary        | int     |
| DepartmentId  | int     |
+---------------+---------+

Write a SQL query to report, for each department, the employees who earn one of the top three highest distinct salaries in that department. Return columns DepartmentId, Employee (the Name), and Salary, ordered by DepartmentId ascending and Salary descending. If a department has fewer than three distinct salaries, include all its employees.

Examples

Input

Employee table:
+----+------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+------+--------+--------------+
| 1  | Joe  | 70000  | 1            |
| 2  | Jim  | 90000  | 1            |
| 3  | Henry| 80000  | 2            |
| 4  | Sam  | 60000  | 2            |
| 5  | Max  | 90000  | 1            |
+----+------+--------+--------------+

Output

+--------------+----------+--------+
| DepartmentId | Employee | Salary |
+--------------+----------+--------+
| 1            | Jim      | 90000  |
| 1            | Max      | 90000  |
| 1            | Joe      | 70000  |
| 2            | Henry    | 80000  |
| 2            | Sam      | 60000  |
+--------------+----------+--------+

Hints

• You need to rank salaries within each department and pick those with rank ≤ 3.
• Use window functions (e.g., DENSE_RANK) or a correlated subquery counting higher distinct salaries.
• Be careful to include all employees who tie on a top‑three salary.

Approach 1 – Window Function (DENSE_RANK)

Partition by department and assign each salary a dense rank in descending order. Then filter to ranks 1 through 3.

SQL Query

SELECT DepartmentId,
       Name       AS Employee,
       Salary
FROM (
  SELECT DepartmentId,
         Name,
         Salary,
         DENSE_RANK() OVER (
           PARTITION BY DepartmentId
           ORDER BY Salary DESC
         ) AS rnk
  FROM Employee
) AS ranked
WHERE rnk <= 3
ORDER BY DepartmentId,
         Salary DESC;

Step‑by‑Step

1. The subquery assigns rnk=1 to the highest salary in each department, rnk=2 to the next distinct salary, and so on.
2. We then select only those rows where rnk≤3.
3. Final ORDER BY ensures grouping by department and listing highest earners first.

Approach 2 – Correlated Subquery

For each employee, count how many distinct salaries in the same department exceed theirs. If fewer than 3, they are in the top three.

SQL Query

SELECT e1.DepartmentId,
       e1.Name     AS Employee,
       e1.Salary
FROM Employee AS e1
WHERE (
    SELECT COUNT(DISTINCT e2.Salary)
    FROM Employee AS e2
    WHERE e2.DepartmentId = e1.DepartmentId
      AND e2.Salary > e1.Salary
) < 3
ORDER BY e1.DepartmentId,
         e1.Salary DESC;

Step‑by‑Step

1. For each e1, the subquery counts distinct salaries in its department that are strictly greater.
2. If that count is 0, 1, or 2, then e1’s salary ranks among the top three distinct.
3. Filter on that condition and sort the results.