mysql select top users problem

Question

i have users table and i have posts table i want select from users the top users that have the big amount of posts from posts table and order them by numbers of posts i can make it by array_count_values() by i cant order it now i think if i make it by one mysql query by left and join will be more better

table structure

posts

id | auther_id

i tried this

SELECT COUNT(1) cnt, u.user_id 
FROM users u 
LEFT JOIN posts p
ON p.author_id=u.user_id
GROUP BY u.user_id
ORDER BY cnt DESC
LIMIT 20

---

it gave me this see the arrow what is this i just have 2 posts under user_id 5 what is this first row

Answer 1

You need to aggregate the posts by user using GROUP BY u.user_id, get a COUNT value for the number of posts and ORDER BY that number, in descending order:

SELECT COUNT(1) cnt, u.user_id 
FROM users u 
LEFT JOIN posts p
ON p.author_id=u.user_id
GROUP BY u.user_id
ORDER BY cnt DESC
LIMIT 20

Answer 2

SELECT u.user_id, COUNT(*) as post_count
FROM   users u
INNER JOIN posts p
USING (user_id)
GROUP BY u.user_id
ORDER BY post_count

Answer 3

i used this and its worked is it true

SELECT COUNT( 1 ) cnt, a.auther_id
FROM `posts` a
LEFT JOIN users u ON a.auther_id = u.id
GROUP BY a.auther_id
ORDER BY cnt DESC
LIMIT 20