How to select the top 3 salaries of the department?

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 |
   +------------+----------+--------+
   

If you added a column Count employees who earn more your table would look like this

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

Then to find the top 3 per dept. your WHERE would be

WHERE `Count employees who earn more` < 3

If you had =3 it would return the only employees that was the 4th highest

Since you don't have that column, that's what this SQL does

(SELECT COUNT(DISTINCT(Salary)) FROM Employee 
       WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary)

If you wanted to produce the table described above you could do the following

SELECT 
      D.Name AS Department, 
      E.Name AS Employee, 
      E.Salary AS Salary,
      Count(E2.Salary) as Count_employees_who_earn_more
FROM Employee E 
INNER JOIN Department D 
ON E.DepartmentId = D.Id 
LEFT JOIN Employee E2 ON 
    e2.DepartmentId = E.DepartmentId
    AND E2.Salary > E.Salary
GROUP BY  D.Name , 
      E.Name , 
      E.Salary 

Demo