How do I only display the highest result in SQL?

Question

I have two tables
department:
CREATE TABLE department
( DEPARTMENT_ID NUMBER(4) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(20),
ADDRESS VARCHAR2(20));
and employee:
CREATE TABLE employee
( EMPLOYEE_ID NUMBER(4) PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(20),
JOB VARCHAR2(20),
MANAGER_ID NUMBER(4),
HIRE_DATE DATE,
SALARY NUMBER(9, 2),
COMMISSION NUMBER(9, 2),
DEPARTMENT_ID NUMBER(4) REFERENCES department(DEPARTMENT_ID));

I am trying to count how many employees each department has and display only the highest result. I've gotten to this point:

select d1.department_name, count(t1.department_id) as number_of_employees
from employee t1, department d1
where t1.department_id(+) = d1.department_id group by d1.department_name, d1.address
order by d1.department_name

this gives me the results I need to know by displaying the department names and how many employees work for each, but how would I go about only displaying the department with the highest number of employees. Using the MAX function was suggested to me, but I don't think you can use it on the count function since it isn't a permanent attribute of the department table. Any help is greatly appreciated.

Answer 1

This is the best I can come up with. Probably a better way.

But I think the jist of the problem is that there might be more than 1 row generated here. 2 departments might have 100 employees, while the rest only have a handful. So you need a query that can generate multiple rows (and you need to be prepared for that possibility).



I used a with clause to gather dept. name and number of employees. I just can't bring myself to use the weird oracle join syntax you used...I prefer the join by..using syntax. Rewrite the inner query here if you must.



Once you have the depts and count, you need to do another group by, this time using a having clause that selects only those depts that have the max number of emps. And to get THAT number, I had to do yet another subquery, but that's just a simple select max(x) from.



with tmp as

(

select DEPARTMENT_NAME,count(*) count

from department

join employee using(DEPARTMENT_ID)

group by DEPARTMENT_NAME

)

select DEPARTMENT_NAME,count

from tmp

group by DEPARTMENT_NAME,count

having count = (select max(count) from tmp);



This is one of those questions where I hope someone posts an answer that makes me do a face-palm...why didn't I think of that simple solution???



HTH



ETA: Untested. I did a very similar query on existing tables I had, but I might have botched the translation into your table/column names. You might have to tweak the code.

Answer 2

SELECT d1.department_name, COUNT(t1.department_id) AS number_of_employees

FROM department d1 LEFT OUTER JOIN employee t1 ON d1.department_id t1.department_id

GROUP BY d1.department_name, d1.address HAVING MAX(COUNT(t1.department_id))

Answer 3

Did you try group by instead?