View previous topic :: View next topic
|
Author |
Message |
k_kirru Currently Banned New User
Joined: 14 Sep 2005 Posts: 16
|
|
|
|
Hi All,
I need a query to the below req..
I have an employee table with say 11 employees. I want to extract the dept number with max no of employees.
Below is the Table:
EMP_NO DEPT_NO
01 D01
02 D02
03 D03
04 D01
05 D03
06 D01
07 D02
08 D01
09 D02
10 D03
11 D01
The above table is just an assumption.
The query should return the DEPT_NO with max no of employees. Ie., D01 5(no of employees in this dept)..
Thanks in advance...
Waiting for your reply.
Regards,
Kiran |
|
Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
Try out this query..I did nt test this one
select deptno, count(*) as emptotal from emp_table
group by deptno
order by emptotal
fetch first row only
hope this helps..
Prajesh |
|
Back to top |
|
|
sainathvinod
New User
Joined: 01 Apr 2008 Posts: 11 Location: Chennai
|
|
|
|
The above quer will fetch only one record even if there are more than one dept having the maximum number of employees. Please try the below query which will fetch all the depts(incase there are more than 1) having the maximum number of employees:-
SELECT DEPT_NO
,COUNT(*)
FROM DEPT
GROUP BY DEPT_NO
HAVING COUNT(*) =
(SELECT MAX(TEMP.A) FROM
(SELECT DEPT_NO
,COUNT(*) A
FROM DEPT
GROUP BY DEPT_NO) TEMP)
WITH UR;
_________________ |
|
Back to top |
|
|
|