The solution you need is a combination of all of the above, it only gets more complicated because the salaries need to be summed first (probably not the best database design to be honest).
Unfortunately SQL Window functions (like
RANK()
) cannot be used in a
WHERE
clause so we need another sub-query or CTE.
This works:
;WITH rankings AS
(
SELECT [Name], Dept, TotSalary, RANK() OVER (PARTITION BY Dept ORDER BY TotSalary DESC) as r
FROM ( SELECT [Name], Dept, SUM(Salary) AS TotSalary
FROM Employee GROUP BY Dept, [Name]) q
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1
This version first sums the salary by department and name (in case people with the same name are in different departments - there's a hint regarding the table schema by the way).
It then RANKs the salaries because, as @Maciej-Los stated, more than one employee could earn the same amount (although @OriginalGriff's solution has a similar effect due to the WHERE clause).
Finally all of that goes into the CTE (it could have been a sub-query) so that we can pick out only the top ranking total salary.
[EDIT - the comments to Solution 3 made me re-read the question and I'v realised that you don't want the highest salary by department, just the highest salary overall. To get that just remove the partition by Dept
;WITH rankings AS
(
SELECT [Name], Dept, TotSalary, RANK() OVER (ORDER BY TotSalary DESC) as r
FROM ( SELECT [Name], Dept, SUM(Salary) AS TotSalary
FROM Employee GROUP BY Dept, [Name]) q
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1