How to order by most results in MySQL?

I have a list of posts with associated author_ids, and I'm selecting a list of posts where each author has at least one published post like this:

SELECT DISTINCT author_id 
FROM posts 
WHERE status = "live" AND author_id NOT IN(0,1)

I would like to sort that list top to bo开发者_如何学Cttom by the amount of posts created, but I don't know how to count them up and sort the list by that count.

Any help?

You would need to use GROUP BY to get the posts count

SELECT author_id, COUNT(*) as posts_count
FROM posts 
WHERE status = "live" AND author_id NOT IN(0,1)
GROUP BY author_id
ORDER BY posts_count DESC;