SQL to select Top/Highest count when using Group By

Question

Can anyone help me with SQL that will return highest count for App_ID. I'm running this SQL which returns following data set.

SELECT COMP_ID, APP_ID, count(*) as cnt 
FROM APP_ACCT_VIEW
GROUP BY COMP_ID, APP_ID

COMP_ID APP_ID                    CNT
cpo1000c    AT                    999
cpo1kact    AT                    895
cpo1kact    CPOPYMTS_Administrative    1020
cpo1000c    CPOPYMTS_HighValue           1900
cpo1kact    CPOPYMTS_HighValue           1020
cpo1000c    CPOPYMTS_Internal            1999
cpo1kact    CPOPYMTS_Internal            1020
cpo1kact    IRCDR                     1020
cpo1000c    IRCDR                     50

But I need SQL to return the top/highest cnt for each APP_ID and need output to look like this.

COMP_ID APP_ID                    CNT
cpo1000c    AT                    999
cpo1kact    CPOPYMTS_Administrative    1020
cpo1000c    CPOPYMTS_HighValue           1900
cpo1000c    CPOPYMTS_Internal            1999
cpo1kact    IRCDR                     1020

Thanks Dipen

Answer 1

In most databases, you can use the row_number() function to do this:

select comp_id, app_id, cnt
from (select t.*, row_number() over (partition by app_id order by cnt desc) as seqnum
      from (SELECT COMP_ID, APP_ID, count(*) as cnt
            FROM APP_ACCT_VIEW
            GROUP BY COMP_ID, APP_ID
           ) t
     ) t
where seqnum = 1

If this is not available, you have to do the calculation of seqnum some other way, such as a correlated subquery.

Answer 2

If you are using SQL-Server 2005 or higher you can use a CTE with the ROW_Number or DENSE_RANK function:

WITH CTE AS
(
   SELECT COMP_ID, APP_ID, 
       CNT = COUNT(*) OVER (PARTITION BY APP_ID, COMP_ID) ,
       RN  = ROW_NUMBER() OVER (PARTITION BY APP_ID ORDER BY CNT DESC) 
    FROM APP_ACCT_VIEW
)
SELECT COMP_ID, APP_ID, CNT
FROM CTE
WHERE RN = 1

If you use DENSE_RANK instead you would get multiple records per APP_ID if they have the same (highest) CNT unlike ROW_NUMBER which always returns one result per group.