Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need a query for which has max records in the table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
k_kirru
Currently Banned

New User


Joined: 14 Sep 2005
Posts: 16

PostPosted: Tue Mar 11, 2008 11:49 am    Post subject: Need a query for which has max records in the table
Reply with quote

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
View user's profile Send private message

Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Tue Mar 11, 2008 12:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sainathvinod

New User


Joined: 01 Apr 2008
Posts: 11
Location: Chennai

PostPosted: Wed Apr 02, 2008 2:05 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us