sql - Get Max Count from Group by -

i have trouble output group function in sql.below details table

i have 1 table table name "checks" have 2 columns pid,cid

 name                                      null?    type  ----------------------------------------- -------- ----------------------------  pid                                                varchar2(20)  cid                                                varchar2(20) 

below rows available

select * checks;  pid                  cid -------------------- -------------------- p1                   c1 p1                   c1 p1                   c2 p1                   c2 p1                   c2 p2                   c1 p2                   c1 p2                   c1 p2                   c1 p2                   c1 p2                   c1 p2                   c2 p2                   c2 p2                   c2 p2                   c2 p2                   c2 

p represents participants , c represents category

question

i need know participant participate more 1 category in category participant participate maximum.(for every participants)

expected result:

pid   cid    count(cid) ---   ---    ----------- p1    c2         3 p2    c1         6 

assuming database system (you haven't specified one, suspect oracle?) supports windowing functions , ctes, i'd write:

;with groups (     select pid,cid,count(*) cnt checks group pid,cid ), ordered (     select pid,cid,cnt,        row_number() on (partition pid order cnt desc) rn,        count(*) on (partition pid) multi     groups ) select pid,cid,cnt ordered rn = 1 , multi > 1 

the first cte (groups) finds counts each unique cid,pid combination. second cte (ordered) assigns row numbers these results based on count - highest count assigned row number of 1. count how many total rows have been produced each pid.

finally, select rows assigned row number of 1 (the highest count) , obtained multiple results same pid.

here's oracle fiddle play with. , here's sql server version (and andriy m producing oracle one)