Find the maximum record count using SQL
Retrieve the maximum count of the record 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.