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;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…