CODEWITHSUNDEEP
sqloracle-database
nagesha c
nagesha c

Reputation: 43

Write a query to display departno and no of employee which departno have max employee.?

Write a query to display departno and no of employee which departno have max employee.?

Here I tried out following query:

select deptno, count(*) as no_of_emp   
from emp 
group by deptno 
order by no_of_emp;

but I was getting as

Deptno   no_of_emp
30           6
20           4
10           4

But I just need first row not all. Is it possible to display only first record in oracle sql?

Upvotes: 1

Views: 96

Answers (3)

Nikhil
Nikhil

Reputation: 3950

Although what you are trying to achieve is can be done by other sql queries changing yo ur query as below will work:

SELECT * from (select deptno, count(*) as no_of_emp   
from emp 
group by deptno 
order by no_of_emp desc) where rownum<=1
;

other query is as follows:

select deptno, count(*) as no_of_emp   
from emp 
group by deptno
having count(*)=(select max(count(*)) as no_of_emp   
from emp 
group by deptno)
order by no_of_emp desc;

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65278

As an alternative you might use max(count(*)) over (order by ...) analytic function with descending count option :

with emp( empno,ename,deptno ) as
(
  select 7839,'KING',10 from dual union all
  select 7698,'BLAKE',30 from dual union all
  select 7782,'CLARK',10 from dual union all
  select 7566,'JONES',20 from dual union all
  select 7788,'SCOTT',20 from dual union all
  select 7902,'FORD',20 from dual union all
  select 7369,'SMITH',20 from dual union all
  select 7499,'ALLEN',30 from dual union all
  select 7521,'WARD',30 from dual union all
  select 7654,'MARTIN',30 from dual union all
  select 7844,'TURNER',30 from dual union all
  select 7876,'ADAMS',20 from dual union all
  select 7900,'JAMES',30 from dual union all
  select 7934,'MILLER',10 from dual
)
select deptno, no_of_emp
from
(
select deptno, count(*) as no_of_emp, 
       max(count(*)) over (order by count(*) desc) as max_populated
  from emp
 group by deptno
 order by no_of_emp
 )
where max_populated = no_of_emp;

 DEPTNO NO_OF_EMP
 ------ ---------
   30       6

Rextester Demo

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may use ROWNUM

select * from
(
select deptno, count(*) as no_of_emp   
  from emp 
 group by deptno 
order by no_of_emp desc
) where rownum = 1;

Or in 12c and above, FETCH..FIRST

select deptno, count(*) as no_of_emp   
      from emp 
     group by deptno 
    order by no_of_emp desc fetch first 1 ROWS ONLY

Upvotes: 1

Related Questions