Group by

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following is my table:

EmpId AutoNumber
FirstName
LastName
DeptId
Salary

This is my query to find the hignest salary in each dept. My question is how
can I find the employee who has the highest salay? I mean can I also get the
EmpId in the same query? I want to use a single statement. Please help.
Thanks.

SELECT Emp.DeptId, max(Emp.Salary)
FROM Emp
GROUP BY emp.deptid
 
One method is to use a correlated sub-query. See the example below.

SELECT Emp.DeptID, Emp.EmpID, FirstName, LastName, Emp.Salary
FROM EMP
WHERE Emp.Salary =
(SELECT MAX(E.Salary)
FROM Emp as E
WHERE E.DeptID = Emp.DeptID)

Note that this can return more than one record (Ties) for each DeptID if more
than one person is making that maximum salary.
 
Back
Top