Top Employee Salary by Department

topsal.sql

/* 
Given 2 tables in a MySQL database, Employee and Department, find the employees (including ties) with the highest 
salaries for each department. 

Employee table: 
Id (auto incremented integer)
Name (Varchar, employee name)
DepartmentId (integer and key to Department table)
Salary (Integer)

Department table: 
Id (auto incremented integer
Name (varchar, department name)
*/ 

Select d.Name as Department, e1.Name as Employee, e2.maxsal as Salary from Employee e1
Join (Select MAX(Salary) as maxsal, DepartmentId from Employee
Group BY DepartmentId) e2
ON e1.Salary = e2.maxsal AND e1.DepartmentId = e2.DepartmentId
Join Department d 
ON e1.DepartmentId = d.Id