Find max value with grouping

Question

I have a users table:

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

I want 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?

You cannot find the user using a simple `GROUP BY` query (with or without `HAVING`). You can use it in a subquery or you can find some inspiration towards the fast and correct solution by reading [this answer](http://stackoverflow.com/a/28090544/4265352). — axiac, Aug 31 '16 at 22:29

score 1 · Answer 1 · answered Aug 31 '16 at 21:33

1

Try

HAVING U.credits = MAX(U.credits)

Just HAVING MAX(u.credits) doesn't make much sense. That'd be like saying HAVING 42 - you're not comparing that value to anything, so ALL rows will be implicitly matched.

answered Aug 31 '16 at 21:33

Marc B

356,200
43
426
500

This query is not working. in example http://sqlfiddle.com/#!9/f646ff/6/0 country=2 lost in result. Since you can not use "Having" on not grouped fields – Mike Aug 31 '16 at 22:02

score 1 · Accepted Answer · answered Aug 31 '16 at 21:34

1

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)

answered Aug 31 '16 at 21:34

Andrej

7,474
1
19
21

Mike · Answer 3 · 2016-08-31T22:31:39.827

SELECT *
  FROM `users`
 WHERE (country,credits)
    IN (
        SELECT country, max(credits)
          FROM users
         GROUP BY country
       )

Test on sqlfiddle.com

Or "hack" with get maximum of concatenated credits-uid and cut uid from it:

SELECT country, 
       substr(max(concat(lpad(credits,10,'0'),uid)),11) as uid,
       max(credits) as credits
  FROM users
 group by country

Test on sqlfiddle.com

The last example is usually the fastest, because it takes place in a single pass on a table, without subqueries.

Find max value with grouping

3 Answers3