user889349 user889349 - 3 months ago 11
MySQL Question

MySQL find max value with grouping

I have a users table:

uid country credits
1 1 10
2 1 8
3 2 4
4 2 6


I wan't to find users with max credits grouped by country.
Example output:

[0] => Array(
'country' => 1,
'uid' => 1,
'credits' => 10
),
[1] => Array(
'country' => 2,
'uid' => 4,
'credits' => 6
)


I have tried (not successful):

SELECT
U.*
FROM
`users` AS U
WHERE
U.user_deleted = 0
GROUP BY
U.country
HAVING
MAX(U.credits)


How can I fix my request?

Answer

Try this query:

SELECT 
   u.*
FROM 
   `users` AS u
WHERE
   u.credits = (select max(u1.credits) from users u1 where u1.country=u.country  group by u1.country)
Comments