Reputation: 35
I need to write SQL query (for Oracle) which displays name of manager with max number of subordinates.
Table's structure:
EMPNO INT PRIMARY KEY
ENAME VARCHAR NOT NULL
MGR_ID INT
MGR_ID employees' attribute is an EMPNO(primary key) of manager.
What I've tried so far:
SELECT ENAME FROM EMP WHERE
(SELECT COUNT(MGR_ID) FROM EMP GROUP BY MGR_ID)=
(SELECT MAX(SELECT COUNT(MGR_ID) FROM EMP GROUP BY MGR_ID) FROM EMP);
Upvotes: 0
Views: 1432
Reputation: 63
The solution is
with temp_tab as
(
select mgr_id, count(*) num_emp from emp group by mgr_id order by 2 desc
)
select * from temp_tab where rownum<=1;
Upvotes: 0
Reputation: 6346
WITH mgr_cnt
AS ( SELECT mgr_id, COUNT (*) cnt
FROM EMP
GROUP BY mgr_id)
SELECT e.ename
FROM emp e,
(SELECT mgr_id, cnt, DENSE_RANK () OVER (ORDER BY cnt DESC) rnk
FROM mgr_cnt) t
WHERE e.empno = t.mgr_id and t.rnk=1;
Another version suggested by a_horse_with_no_name
SELECT e.ename FROM
emp e
INNER JOIN
(
SELECT mgr_id, DENSE_RANK () OVER (ORDER BY COUNT (*) DESC) rnk
FROM emp
GROUP BY mgr_id
)list
ON ( list.mgr_id=e.empno)
WHERE list.rnk=1
PS : Query Not Tested
Upvotes: 1