CODEWITHSUNDEEP
sql
Mykhailo Rybak
Mykhailo Rybak

Reputation: 173

SQL select MAX(COUNT)

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.

Upvotes: 9

Views: 83696

Answers (5)

Nikhil
Nikhil

Reputation: 2308

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

Upvotes: 4

Meilin He
Meilin He

Reputation: 69

It's pretty simple, you can try:

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

Upvotes: 1

iceheaven31
iceheaven31

Reputation: 887

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

Upvotes: 0

opalenzuela
opalenzuela

Reputation: 3171

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.

Upvotes: 16

GeertG
GeertG

Reputation: 158

maybe like this:

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)

Didn't test it, but it might work

Upvotes: 0

Related Questions