SQLUSA
 

Microsoft SQL Server 2000

Best Practices

 

How to find the highest paid employee in each department?

 

This is tricky. What you find with a group by is the highest pay. You have to a join back to the original table from the results of group by. Assume standard naming conventions.

select Department, FirstName, LastName, MaxSalary
from (select Department, MaxSalary=max(salary)
from Employee group by Department) a, Employee b
where a.Department=b.Department
and a.MaxSalary=b.Salary

 

Best in the World in SQL Server Training
 
SQLUSA.com Home Page