How to MAX(COUNT(x)) in SQLite

Question

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

If I want to know how many blog posts every blog has:

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

What do I do if I want to know how many posts the blog with the most posts has? (I don't need the blog_id.) Apparently this is illegal:

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

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

Answer 1

Other solution:

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

I'm not sure which solution would run faster, if this one or the one with the subquery.

Answer 2

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

1. get the number of posts for each blog
2. 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 the (...)).

NB: I'm not a SQLite power user and so I don't know if this works, but the SQLite docs indicate that subqueries are supported.