CODEWITHSUNDEEP
mysqlsql
Graham
Graham

Reputation: 1473

do a query to get top 3 occurrences of a user_id id in rows of data?

I want to do a query where I get the top 3 contributing authors i.e. they wrote the most pages / posts. I'd select the data by the session_id of that user associated to each row i.e. a page they wrote. I want to select and order the top 3 people who have the most rows in the DB. How can I query this? I was thinking...

SELECT DISTINCT user_id
FROM music_spot 
WHERE (need a condition here)
ORDER BY (the person who contributed the most pages to the third
LIMIT 3

How could I do something like this? Thank you.

Upvotes: 1

Views: 131

Answers (3)

Swatantra Kumar
Swatantra Kumar

Reputation: 1330

SELECT user_id, COUNT(row_id)
FROM  music_spot 
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
LIMIT 0,3

Upvotes: 0

Manatok
Manatok

Reputation: 5696

SELECT user_id, COUNT(post_id)
FROM  music_spot 
GROUP BY UserID
ORDER BY COUNT(user_id) DESC
LIMIT 0,3

Upvotes: 1

juergen d
juergen d

Reputation: 204746

SELECT user_id
FROM music_spot 
WHERE session_id = 123
ORDER BY count(user_id) desc
group by user_id
LIMIT 3

Upvotes: 2

Related Questions