CODEWITHSUNDEEP
sqloracle-database
HadJower5
HadJower5

Reputation: 35

Find name of manager with max number of subordinates

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

Answers (2)

Prabhat Bhatt
Prabhat Bhatt

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

Gaurav Soni
Gaurav Soni

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

Related Questions