CODEWITHSUNDEEP
sqloracle-databasecountmax
user3308470
user3308470

Reputation: 107

select id that occur the most

I have two tables

Users(user_id, name)            

Competition(comp_id, user_id, score)

I need to select user_id that occur the most in competition. Can I do it with max(count)?
Is there any way to get exception if two users have same most occurances?

I tried:

SELECT MAX(numberr), USER_ID 
FROM ( 
   SELECT USER_ID, COUNT(COMP_ID) AS numberr 
   FROM COMPETITION 
   GROUP BY USER_ID
) 
GROUP BY USER_ID;

But result I get is:

MAX(numberr) USER_ID   
4             1   
2             2   
6             3

Upvotes: 0

Views: 88

Answers (3)

dnoeth
dnoeth

Reputation: 60482

To get multiple users with the max count use a RANK:

SELECT numberr, USER_ID 
FROM ( 
   SELECT USER_ID, COUNT(COMP_ID) AS numberr 
     ,RANK() OVER (PARTITION BY USER_ID ORDER BY COUNT(COMP_ID) DESC) rnk
   FROM COMPETITION 
   GROUP BY USER_ID
) dt
WHERE rnk = 1;

Upvotes: 0

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

You can do it with not exists. If there are two users with the same max count, both are presented.

with temp as
(
   SELECT USER_ID, COUNT(COMP_ID) AS numberr 
   FROM COMPETITION GROUP BY USER_ID
) 
select *
from temp t 
where not exists (select 1 from temp where numberr > t.numberr)

SQLFiddle

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

You can get it done with COUNT:

SELECT *
FROM (SELECT USER_ID,COUNT(comp_id) as numberr
      FROM COMPETITION
      GROUP BY USER_ID
      ORDER BY COUNT(comp_id) DESC) T1
WHERE rownum=1;

Upvotes: 1

Related Questions