The Employee table holds all employees. Every employee has an Id, a salary, 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 | +----+-------+--------+--------------+
The Department table holds all departments of the company.
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+
JOIN and IN clause [Accepted]Algorithm
\nSince the Employee table contains the Salary and DepartmentId information, we can query the highest salary in a department.
\nSELECT\n DepartmentId, MAX(Salary)\nFROM\n Employee\nGROUP BY DepartmentId;\n
\n\nNote: There might be multiple employees having the same highest salary, so it is safe not to include the employee name information in this query.
\n
| DepartmentId | MAX(Salary) |\n|--------------|-------------|\n| 1 | 90000 |\n| 2 | 80000 |\n
Then, we can join table Employee and Department, and query the (DepartmentId, Salary) are in the temp table using IN statement as below.
MySQL
\nSELECT\n Department.name AS \'Department\',\n Employee.name AS \'Employee\',\n Salary\nFROM\n Employee\n JOIN\n Department ON Employee.DepartmentId = Department.Id\nWHERE\n (Employee.DepartmentId , Salary) IN\n ( SELECT\n DepartmentId, MAX(Salary)\n FROM\n Employee\n GROUP BY DepartmentId\n )\n;\n
| Department | Employee | Salary |\n|------------|----------|--------|\n| Sales | Henry | 80000 |\n| IT | Max | 90000 |\n