SQL selects MAX (COUNT)

Question

I am trying to select a user who has MAX microposts counters:

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

and it 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 choose only "Example User";53, (a user who has MAX number of microflows)

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

Answer 1

I would try with ORDER BY max DESC LIMIT 1, where the maximum is the count (*) field. Sort of:

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 don't have mysql now, so I am doing this on paper (and this may not work), but this is 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

Answer 3

:

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

,

Answer 4

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

Answer 5

, :

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