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

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

Answer 2

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