Query help
Posted: Mon Nov 29, 2010 10:58 pm
I am trying to write a query that returns the number of employees by location and the highest paid employee in that location. Employee name, department id and salary are listed in the employees table, and are joined to the cities table by department id. I know this is probably rife with errors, but I am very new to this and trying to learn, so any help would be appreciated. Thanks.
select c.city, count(e.employee_id) , e.high_name
from cities c, employees e
where c.department_id = e.department_id
and high_emp = (select concat(first_name, last_name) as high_name, salary from employees order by salary limit 1);
select c.city, count(e.employee_id) , e.high_name
from cities c, employees e
where c.department_id = e.department_id
and high_emp = (select concat(first_name, last_name) as high_name, salary from employees order by salary limit 1);