Geting the most count by id

I have a forum. I want to perform an SQL query so I can get the user who has the most blog submissions. I have 2 tables, blogs and users.

The blogs table has author_id which is the user_id from the users table.

So here is what I tried to do but without success:

SELECT b.author_id FROM blogs b 
INNER JOIN users u ON b.author_id = u.user_id 
ORDER BY count(author_id) DESC 
GROUP BY b.author_id 
LIMIT 0,10;

Could you please help me to find the user with most blog submissions, or in o开发者_开发技巧ther words, I need to find a author_id that has most occurrence in the blogs table. Thanks!

You don't need to join to users for this, you just need a GROUP BY and a LIMIT:

SELECT author_id
FROM blogs
GROUP BY author_id
ORDER BY COUNT(*) DESC
LIMIT 1

The GROUP BY tells the COUNT which groups it should count; in this case, it should collect up the blogs for each author_id and count those as a single group.

SELECT COUNT(a.*) AS total, a.author_id
FROM blogs AS a
GROUP BY author_id
ORDER BY total DESC
LIMIT 1

The different parts of your query are in the wrong order. This should work

SELECT b.author_id
FROM blogs b
INNER JOIN users u ON b.author_id = u.user_id
GROUP BY b.author_id
ORDER BY COUNT( b.author_id ) DESC 
LIMIT 0 , 10

See the MySQL documentation regarding the SELECT Syntax.

Also, I assume that your example is a bit simplified since there is, as others have noted, really no need for a join in this case.