Find the maximum record count using SQL

Following the code provided below will enable you to retrieve the maximum count of the record using SQL -- since there is no MAX(COUNT(FLD)) in DB2.

Supposing you have a table with two fields book IDs and an employee ID.

 TRBKID   TREMPID
    77    22,222
    99     1,000
10,001       999
67,804    55,555
67,818     2,001
 1,111     1,111
 1,112     1,111
 1,113     1,111
 1,114     1,111  

From the data above, I can see that employee ID 1111 is the one with the most book IDS against his name. If we want to know which employee has the maximum books against his name, naturally we think of MAX(COUNT(EMPID). Since we cannot have this in SQL, here is the SQL code for such a scenario.

Select TREMPID, count(TREMPID) from Table group by TREMPID having count(trempid)=(Select max(A.CNT) from (Select count(TREMPID) as CNT from Table group by (TREMPID)) as A)

This will return:

 
TREMPID           COUNT
  1,111               4 

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.