# SELECT, MAX(), Group by

### Question

•

Hi, I have two tables Employee and Department.

Employee                            Department

-EmployeeID                   -DepartmentID

-EmployeeName             -DepartmentName

-Salary

-DepartmentID

I want to SELECT each departments EmployeeName, who has max salary.

SELECT EmployeeName,MAX(Salary) FROM Employee GROUP BY DepartmentID

I know it won't work. I use CURSOR to solve this. But is there any easy way to do this. Using SELECTs

.

Thursday, February 28, 2008 2:11 AM

• Code Snippet

SELECT e.EmployeeName, e.DepartmentID

FROM Employee e

JOIN (SELECT DepartmentID, MAX(Salary) AS MaxSalary

FROM Employee

GROUP BY DepartmentID) em ON e.DepartmentId = em.DepartmentID AND e.Salary = em.MaxSalary

Note that if two employees have the same (max) salary for a department, it will list both of them.  Effectively, the derived table (the part in the brackets) is creating a listing of each department, with the highest salary in the department.  Then you JOIN (if you don't specify, it's an INNER JOIN) it back to the employee table, so you're only showing employees for each department whose salary is equal to the max salary.

Make sense?

Thursday, February 28, 2008 2:48 AM
• Using Window operator & CTE – for SQL Server 2005,

Code Snippet

;with cte

as

(

select

e.employeename,

e.departmentid,

e.salary,

max(e.salary) over (partition by e.departmentid) as maxsalary

from

employee e

)

select employeename, departmentid, salary

from

cte

where

salary = maxsalary

Thursday, February 28, 2008 3:27 AM

### All replies

• Code Snippet

SELECT e.EmployeeName, e.DepartmentID

FROM Employee e

JOIN (SELECT DepartmentID, MAX(Salary) AS MaxSalary

FROM Employee

GROUP BY DepartmentID) em ON e.DepartmentId = em.DepartmentID AND e.Salary = em.MaxSalary

Note that if two employees have the same (max) salary for a department, it will list both of them.  Effectively, the derived table (the part in the brackets) is creating a listing of each department, with the highest salary in the department.  Then you JOIN (if you don't specify, it's an INNER JOIN) it back to the employee table, so you're only showing employees for each department whose salary is equal to the max salary.

Make sense?

Thursday, February 28, 2008 2:48 AM
• Using Window operator & CTE – for SQL Server 2005,

Code Snippet

;with cte

as

(

select

e.employeename,

e.departmentid,

e.salary,

max(e.salary) over (partition by e.departmentid) as maxsalary

from

employee e

)

select employeename, departmentid, salary

from

cte

where

salary = maxsalary

Thursday, February 28, 2008 3:27 AM
• Yes, thanks you. It is amazing. Now I don't need CURSOR. thank you.

• Proposed as answer by Saturday, December 17, 2011 6:37 AM
Monday, March 03, 2008 11:24 AM