mysql select top users problem

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 number开发者_如何学Cs 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 alt text http://img511.imageshack.us/img511/6707/31154352.gif see the arrow what is this i just have 2 posts under user_id 5 what is this first row

---

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

---

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

---

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