CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Geo
Geo

Reputation: 3200

Select the biggest value

I am trying to solve a simple problem but i am getting stack on the details.

I have 2 tables, one has employees and the other one has departments. My problem: I am trying to check which department has the most employees and output only that specific department.

So far I have:

select count(*) Number_of_employees
      from department d, employee e
      where d.department_id = e.department_id
      group by department_name

which outputs:

NUMBER_OF_EMPLOYEES    
---------------------- 
2                      
4                      
3                      
3                      
3

My goal is to to output only the department with the most employees which is the department with 4 employees.

I tried using the MAX and JOIN but i am not so good with join yet so any suggestions will be appreciated.

Upvotes: 0

Views: 145

Answers (3)

Zsolt Botykai
Zsolt Botykai

Reputation: 51653

 select department_name from 
        ( select department_name, count(*) Number_of_employees
            from department d, employee e
           where d.department_id = e.department_id
           group by department_name
           order by 2 desc ) 
  where rownum = 1

should do.

HTH

Upvotes: 1

vasdee
vasdee

Reputation: 161

You could do it this way to avoid the rownum:

select 
      max(d.department_name) keep (dense_rank first order by count(1) desc) as department_name
      , count(1) as number_of_employees
    from employee e
    inner join department d on (e.department_id = d.department_id)
    group by d.department_name
    ;

Upvotes: 3

steve godfrey
steve godfrey

Reputation: 1234

@Zsolt Botykai

I think this is correct, apart from order by needs to be DESC, and I don't think you can refer to number_of_employees inside the query. ( you can't in oracle anyway ).

  select department_name 
  from
     (select department_name
            ,number_of_employees
      from  
        ( select department_name, count(*) Number_of_employees
          from department d, employee e
          where d.department_id = e.department_id
          group by department_name) 
      order by Number_of_employees DESC) 
  where rownum = 1

Upvotes: 3

Related Questions