CODEWITHSUNDEEP
sqlsqlitecountgroup-by
Rudie
Rudie

Reputation: 53821

How to MAX(COUNT(x)) in SQLite

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...

Upvotes: 6

Views: 9662

Answers (2)

Matt Fenwick
Matt Fenwick

Reputation: 49085

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.

Upvotes: 6

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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.

Upvotes: 6

Related Questions