Mysql – select department(s) with maximum number of employees


I have two tables EMP(id,name,DEPT_id) and DEPT(id ,name). I need to find the department(s) in which the maximum number of employees work. Please help.

Best Solution

This will give the department name of the department which is having maximum number of employees.

Select DEPT_NAME from department where DEPT_ID = (select DEPT_ID from (Select DEPT_ID, count(DEPT_ID) from Employee group by DEPT_ID order by count(DEPT_ID) desc) where rownum = 1);