I need a Query to find the list of employees in a department having maximum number of employees. Though I am able to do it with the query written below, I am sure there would be a better query. Can any one help please.
select empno, ename, sal, a.deptno
from emp a,
(select deptno from (select deptno, count(1) over (partition by (deptno)) from emp order by 2 desc) where rownum =1) b
where a.deptno = b.deptno;
Thanks in advance.