MySQL selects top rows with identical condition values

Question

I do not know what to name this problem. Correct me if you have better words.

I have two tables, users and posts.

Users

id | username | password | ... 

Posts:

 id | author_id | title | content | ... 

Now I want to list the "most active" users - the users who wrote most of the posts. And specifically, I want a top 10 result.

 SELECT u.username, COUNT(p.id) AS count FROM Posts p, Users u WHERE u.id=p.author_id GROUP BY p.author_id ORDER BY count DESC LIMIT 10; 

I can get the expected result. However, the rating cannot be “fair” if some users have the same number of posts.

For example, I can get results such as:

 User 1 | 14 User 2 | 13 ... User 9 | 4 User 10 | 4 

There are several more users who have 4 posts.

So top 10 may not be exactly 10 . How can I get a more “fair” result that contains additional rows of users who have 4 posts?

Answer 1

This is the right decision, I think: you need a subquery to find out how many posts are in 10th place in the top ten. Then you use an external query to fetch users with almost this newsletter.

 SELECT u.username, COUNT(p.id) AS count FROM Posts p JOIN Users u ON u.id = p.author_id GROUP BY p.author_id HAVING COUNT(p.id) >= ( SELECT COUNT(p.id) AS count FROM Posts p JOIN Users u ON u.id = p.author_id GROUP BY p.author_id ORDER BY count DESC LIMIT 9, 1 ) ORDER BY count DESC 

Answer 2

This may not be the best solution.

 select u.username, COUNT(p.id) AS count FROM Posts p join Users u on u.id = p.author_id GROUP BY p.author_id having COUNT(p.id) in ( SELECT COUNT(p.id) FROM Posts p join Users u on u.id = p.author_id GROUP BY p.author_id ORDER BY count DESC LIMIT 10 ) ORDER BY count DESC 

Answer 3

Try the following:

 SELECT username, PostCount FROM (SELECT username, PostCount, IF(@PostCount = @PostCount:=PostCount, @idx: =@idx +1, @Idx:=1) AS idx FROM (SELECT u.username, COUNT(p.id) AS PostCount FROM Posts p INNER JOIN Users u ON u.id=p.author_id GROUP BY p.author_id ) AS A, (SELECT @PostCount:=0, @Idx:=1) AS B ORDER BY PostCount DESC ) AS A WHERE idx <= 10;