How to Max in SQLite (count (x))

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

If I want to know how much each blog has Blog posts:

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

What if I want to know how many posts have the most posts ? (I don’t need blog_id.) Obviously this is illegal:

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

I am very Sure I missed something, but I can’t see it…

Other options:

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

< p>I’m not sure which solution runs faster, if this or a subquery solution.

I have a SQLite table b​​log_posts. Every blog post There is an id and blog_id.

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

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

What if I want to know how many posts have the most posts? (I don’t need blog_id.) Obviously this is illegal:

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

I am very I’m sure I missed something, but I can’t see it…

Other solutions:

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

I’m not sure which solution runs faster , If this or subquery is a solution.

Leave a Comment

Your email address will not be published.