CODEWITHSUNDEEP
sql
Alex
Alex

Reputation: 3839

SQL select statement to find ID's that occur the most

Basic SQL statement question -

I have a table (myUsers) that contains the column "UserID." The same UserID can appear one to many times in these rows. I'm looking for a query that will give me back the specific userIDs that appear the most in this table, and also with their count. Any thoughts?

Thanks in advance!

Upvotes: 7

Views: 8091

Answers (2)

kͩeͣmͮpͥ ͩ
kͩeͣmͮpͥ ͩ

Reputation: 7846

select UserID, count(UserID)
from myUsers
group by UserID
order by count(UserID) desc

Upvotes: 31

Wil P
Wil P

Reputation: 3371

DECLARE @THRESHOLD INT
SET @THRESHOLD = 20
SELECT UserID, COUNT(*)
FROM MYUSERS
GROUP BY UserID
HAVING COUNT(*) > @THRESHOLD
ORDER BY COUNT(*) DESC

EDIT: I changed from where to having, duh totally forgot about that. :)

Upvotes: 2

Related Questions