Get Max Count from Group by

Question

I have trouble to get a output for group function in sql.Below are the details for table

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

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PID                                                VARCHAR2(20)
 CID                                                VARCHAR2(20)

Below are rows available

select * from 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 and c represents category

question

I need to know which participant participate more than one category in that which category participant participate maximum.(for every participants)

Expected result:

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

Answer 1

Assuming a database system (you haven't specified one, but I suspect Oracle?) that supports windowing functions and CTEs, I'd write:

;With Groups as (
    select pid,cid,COUNT(*) as cnt from checks group by pid,cid
), Ordered as (
    select pid,cid,cnt,
       ROW_NUMBER() OVER (PARTITION BY pid ORDER BY cnt desc) as rn,
       COUNT(*) OVER (PARTITION BY pid) as multi
    from Groups
)
select pid,cid,cnt
from Ordered
where rn = 1 and multi > 1

The first CTE (Groups) just finds the counts for each unique cid,pid combination. The second CTE (Ordered) assigns row numbers to these results based on the count - with the highest count assigned a row number of 1. We also count how many total rows have been produced for each pid.

Finally, we select those rows which were assigned a row number of 1 (the highest count) and for which we obtained multiple results for the same pid.

Here's an Oracle fiddle to play with. And here's an SQL Server version (and thanks to Andriy M for producing the Oracle one)