CODEWITHSUNDEEP
mysql
Ekin
Ekin

Reputation: 1955

Select MAX() and respective id

SELECT MAX(count),id_university FROM (
    SELECT COUNT(*) as count, id_university FROM `users` 
    where status = 2 AND id_university>0 
    GROUP BY id_university
)AS counts

Currently above query gives me id_university = 1 instead the respective value.

How can I get the respective id_university with the maximum count?

Upvotes: 0

Views: 36

Answers (1)

Barmar
Barmar

Reputation: 780688

Use ORDER BY and LIMIT

SELECT COUNT(*) AS count, id_university
FROM users
WHERE status = 2 AND id_university > 0
GROUP BY id_university
ORDER BY count DESC
LIMIT 1

Upvotes: 2

Related Questions