[Solved] Get the minimum employees with a given job

by


Here the key is to get the count of Employee doing particular job in each department. In below query, this is achieved by subquery. Then, we want to get the Department with minimum no. of employee doing that job so we ordered the records returned by subquery in ascending and then select the first result by using rownum = 1

SELECT DEPTNO from (
SELECT COUNT(*) AS NO_OF_EMP , DEPTNO
FROM EMPLOYEE EMP
WHERE EMP.JOBNAME = 'Analyst'
GROUP BY DEPTNO
ORDER BY NO_OF_EMP ASC )
where ROWNUM = 1;

1

solved Get the minimum employees with a given job