Get the minimum employees with a given job

Question

I have this table:

Name                       Null?    Type
-------------------------- -------- ------------
EMPLOYEENO                 NOT NULL NUMBER(4)
ENAME                               VARCHAR2(15)
JOB                                 VARCHAR2(15)
MGR                                 NUMBER(4)
HIREDATE                            DATE
SAL                                 NUMBER
COMM                                NUMBER
DEPTNO                              NUMBER(2).

I want to get the department with minimum employees who have a given job (for example all the employees with 'Analyst' job).

Can you please help me with the query?

Answer 1

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;