Oracle SQL query issue

Question

I am using oracle's SQL Developer. To begin with, I have this table:

Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25)  
DEPARTMENT_ID           NUMBER(4)

I would like to retrieve(select) the ID of the department in which are the most employees. I managed through a statement to retrieve all the numbers of the employees in every department:

select count(employee_id), department_id
from employees
group by department_id;

It gives me something like:

count(employee_id)   |     department_id
---------------------|------------------
      6                       100
     16                        30
      1                        12

What I would like to do is ONLY to retrieve the department_id 30, which (in this case) has the most employees.

Gordon Linoff · Accepted Answer · 2013-05-28 20:21:28Z

1

A typical way to do this in Oracle:

select department_id
from (select count(employee_id), department_id
      from employees
      group by department_id
      order by count(employee_id) desc
     ) t
where rownum = 1;

If you have potential duplicates and want all the department ids, then a join to the max or analytic function is a better approach. For example:

select department_id
from (select count(employee_id), department_id,
             rank() over (order by count(employee_id) desc) as seqnum
      from employees
      group by department_id
     ) t
where seqnum = 1;

edited May 28, 2013 at 20:21

answered May 28, 2013 at 20:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Teo Over a year ago

Thank you. The first snippet works perfectly but the second one gives me more than one result.

Gordon Linoff Over a year ago

@Theo. . . . The desc will fix that problem (I actually fixed it before I read your comment).

Collectives™ on Stack Overflow

Oracle SQL query issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related