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