Group by

[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

 

[John Spencer]

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.