Like MAX (COUNT (x)) in SQLite

Question

I have an SQLite blog_posts table. Each blog post has id and blog_id .

If I want to know how many blog posts there are in each blog:

 SELECT blog_id, count(1) posts FROM blog_posts group by blog_id 

What should I do if I want to know how many posts there are on the blog with most posts? (I don't need blog_id .) This seems to be illegal:

 SELECT max(count(1)) posts FROM blog_posts group by blog_id 

I'm sure something is missing, but I don’t see it ...

Answer 1

Another solution:

 select count(*) as Result from blog_posts group by blog_id order by Result desc limit 1 

I'm not sure which solution will work faster if this or the one that has the subquery.

Answer 2

You can use a subquery. Here's how you do it:

• get the number of posts for each blog
• select the maximum number of posts

Example:

 select max(num_posts) as max_posts from ( select blog_id, count(*) as num_posts from blog_posts group by blog_id ) a 

(The subquery is in (...) ).

NB: I am not a SQLite user and therefore do not know if this works, but SQLite docs indicates that subqueries are supported.