Getting employee name listed with the salary

August 16, 2009 at 21:59:17
Specs: Windows XP
I have a table which has fields employee_first_name, employee_last_name, department, salary and start_date.
I need to write a SQL statement which will return all highest paid employees for each department. I used GROUP BY. It works when only department is used in GROUP BY clause. Any attempt to include employee_first_name, employee_last_name fails with error message - group function is not allowed here.
Any help is appreciated!

See More: Getting employee name listed with the salary

Report •


#1
August 20, 2009 at 07:02:24
I would suggest 2 queries.
The first to get the MaxSalaryByDepartment, and
the second to get the Details for those values returned by the first query.

In MS Access,
First query with name EmpMaxByDept

SELECT empData.department, Max(empData.salary) AS MaxOfsalary
FROM empData
GROUP BY empData.department;

NOTE: I have used empData as "your" table name.

Second query with name FinalQueryDetails

SELECT empData.employee_first_name, empData.employee_last_name, empData.startDate, empData.salary, empData.department
FROM empData, EmpMaxByDept
WHERE
(((empData.salary)=[EmpMaxbyDept].[MaxOfSalary])
AND
((empData.department)=[EmpMaxByDept].[department]));


Report •

#2
August 20, 2009 at 07:07:36
I would suggest 2 queries.
The first to get the MaxSalaryByDepartment, and
the second to get the Details for those values returned by the first query.

In MS Access,
First query with name EmpMaxByDept

SELECT empData.department, Max(empData.salary) AS MaxOfsalary
FROM empData
GROUP BY empData.department;

NOTE: I have used empData as "your" table name.

Second query with name FinalQueryDetails

SELECT empData.employee_first_name, empData.employee_last_name, empData.startDate, empData.salary, empData.department
FROM empData, EmpMaxByDept
WHERE
(((empData.salary)=[EmpMaxbyDept].[MaxOfSalary])
AND
((empData.department)=[EmpMaxByDept].[department]));

You only have to run FinalQueryDetails to get the result you want.

Good luck.


Report •

Related Solutions


Ask Question