In my mysql db I have an employee table (EMP
) and a department table (DEPT
) and want to find employees with highest salary in each department.
So first I grouped the departments like this
SELECT
dept.DNAME as department,
MAX(emp.SAL) as maximum_sal
FROM
`EMP` emp
JOIN `DEPT` dept ON emp.DEPTNO = dept.DEPTNO
GROUP BY
dept.DNAME
ORDER BY
maximum_sal DESC;
Now to get the employees associated with these departments I have tried doing this:
SELECT
emp.ENAME,
emp.SAL,
department
FROM
`EMP` emp
WHERE
(department, maximum_sal) IN (
SELECT
dept.DNAME as department,
MAX(emp.SAL) as maximum_sal
FROM
`EMP` emp
JOIN `DEPT` dept ON emp.DEPTNO = dept.DEPTNO
GROUP BY
dept.DNAME
ORDER BY
maximum_sal DESC
);
But I get the error Unknown column 'department' in 'field list'
What am I doing wrong? How do I get fields from the inner subquery to show up in my select outer query.
No matter the MySQL version you can do this:
SELECT emp.ENAME,
emp.SAL,
dept.dname
FROM `EMP` emp
JOIN `DEPT` dept ON emp.DEPTNO = dept.DEPTNO
JOIN
(
SELECT DEPTNO, max(sal) as msal
FROM emp
GROUP BY DEPTNO
) tmp ON tmp.DEPTNO = emp.DEPTNO
AND tmp.msal = emp.sal
GROUP BY dept.DNAME
Your query won't work since your "subquery" is only part of an IN clause which you cannot reference from outside.