Six ways to solve this
Start with defining a common table expression for the join which I\'ll reuse for all solutions\n\nwith DepartmentSalary\nas\n(\nselect d.Name as Department, e.N
Start with defining a common table expression for the join which I\'ll reuse for all solutions\n```\nwith DepartmentSalary\nas\n(\nselect d.Name as Department, e.Name as Employee, e.Salary as Salary\nfrom Employee e join Department d on\n    e.DepartmentId = d.Id\n)\n```\n\nSolution 1: \n```\nselect * from DepartmentSalary ds\nwhere not Salary < ANY (select Salary from DepartmentSalary where Department = ds.Department)\n```\n\nSolution 2:\n```\nselect * from DepartmentSalary ds\nwhere Salary >= All (select Salary from DepartmentSalary where Department = ds.Department)\n```\n\nSolution 3:\n```\nselect * from DepartmentSalary ds\nwhere Salary = (select max(Salary) from DepartmentSalary where Department = ds.Department)\n```\n\nSolution 4: \nThis is probably the most optimal, since this will only do 1 scan of the table (Others will need to rely on the query optimizer, which if it\'s smart will hopefully figure out how to rewrite the query to a similar form). \n```\nselect Department, Employee, Salary from (\nselect *, max(Salary) over (partition by Department) maxSalary from DepartmentSalary\n)tmp where Salary = maxSalary\n```\n\nSolution 5:\n```\nselect * from DepartmentSalary ds\nwhere Salary = (select max(Salary) from DepartmentSalary where Department = ds.Department)\n```\n\nSolution 6:\n```\nselect ds.* from DepartmentSalary ds\njoin\n(\n    select Department, max(Salary) maxSalary from DepartmentSalary\n    group by Department \n) maxSalary\non\nds.Department = maxSalary.Department\nand\nds.Salary = maxSalary.maxSalary\n```
[]