184. Department Highest Salary

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

Solution

SELECT Name as Department, Employee, Salary
FROM Department 
JOIN (SELECT Employee.Name AS Employee, Employee.Salary, Employee.DepartmentId
      FROM Employee 
      INNER JOIN 
      (SELECT DepartmentId, MAX(Salary) AS Salary
              FROM Employee
              GROUP BY DepartmentId) t1 ON t1.DepartmentId = Employee.DepartmentId AND t1.Salary = Employee.Salary) t2 ON Department.Id = t2.DepartmentId

Note:   
1. 这道题在解的时候要先选择department表格，因为这个表格比employee表格小，在外面会更快。
2. 两次join 都可以用inner join 或者 join，但递交leetcode时间显示是外面用join 里面用inner join更快。

---

Note on Markdown:
1. ```昨天高亮部分的三点是copy的模板里的，今天知道了它从键盘上哪儿来，就是敲那个跟 ~ 在一起的键六次：敲三次，然后空格留白，再敲三次，这样6个点覆盖的部分就高亮了。
2. CSDN页面字体显示很崩溃，代码在他们的编辑器里显示是courier的样子，但是到网页上就变成了类似times 的字体 ==！