SQL select MAX(COUNT)

Question

I'm trying to select the user who has the MAX microposts count:

SELECT "name", count(*) FROM "users" 
  INNER JOIN "microposts" ON "microposts"."user_id" = "users"."id"
  GROUP BY users.id

and this returns

"Delphia Gleichner";15
"Louvenia Bednar IV";10
"Example User";53
"Guadalupe Volkman";20
"Isabella Harvey";30
"Madeline Franecki II";40

But I want to select only "Example User";53, (user who has MAX microposts count)

I tried to add HAVING MAX(count*) but this didn't work.

Answer 1

I'd try with a ORDER BY max DESC LIMIT 1, where maximum is the count(*) field. Something like:

SELECT "name", count(*) maximum FROM "users" 
   INNER JOIN "microposts" ON "microposts"."user_id" = "users"."id" 
GROUP BY users.id 
ORDER BY maximum DESC 
LIMIT 1

I dont' have mysql available now, so I'm doing this on the paper (and it might not work), but it's just an orientation.

Answer 2

SELECT x.name, MAX(x.count)
FROM (
 SELECT "name", count(*)
  FROM "users" INNER JOIN "microposts" ON "microposts"."user_id" = "users"."id"
  GROUP BY users.id
) x
GROUP BY x.name