MySQL: Reference aggregate function in conditional select

Question

I'd like to reference aggregate function like this:

Sample Data:

+-----------+----------+-------+
| member_id | group_id | score |
+-----------+----------+-------+
|         1 |        1 |    12 |
|         2 |        1 |     2 |
|         3 |        1 |    20 |
|         4 |        2 |    40 |
|         5 |        2 |     4 |
|         6 |        3 |    19 |
|         7 |        3 |    17 |
+-----------+----------+-------+

Query:

SELECT MAX(IF(score = MAX(score), member_id, NULL)) AS member
FROM members
GROUP BY group_id

Expected Result:

+-----------+
| member_id |
+-----------+
|         3 |
|         4 |
|         6 |
+-----------+

Fiddle: http://sqlfiddle.com/#!9/08af68/2

But this results in the error:

Invalid use of group function

I've simplified things here but I'm trying to use this in a much more complex scenario where the score value is the result of a large complex query. Doing this the standard way which I'm aware of results in something that looks a bit ridiculous since the initial query to get the working values is upwards of 35 lines of code.

QUESTION: Why are aggregate functions not allowed to be used like this? Is there a workaround that doesn't involve effectively duplicating the entire SELECT simply to get the MAX() value for use as a point of reference?

Answer 1

Cleaver. But no go.

To achieve what you ask for would take two passes over the data. Things don't work that way.

The first pass is to find the max for each group; the second pass is to check all the value against the maxes.

You can do it with a subquery, an @variable or several other ways. Follow the tag to see ways to do "groupwise max".

Answer 2

It seems that max can't use twice for same attribute.

So, you are expecting member_id of row selected.

As below

+-----------+----------+-------+
| member_id | group_id | score |
+-----------+----------+-------+
|         3 |        1 |    20 |
|         4 |        2 |    40 |
|         6 |        3 |    19 |
+-----------+----------+-------+

But result is different.

+-----------+----------+-------+
| member_id | group_id | score |
+-----------+----------+-------+
|         1 |        1 |    20 |
|         4 |        2 |    40 |
|         6 |        3 |    19 |
+-----------+----------+-------+

Because score max value is 20 for group_id 1. However member_id is selected first row value.

Change like this

SELECT 
  ( SELECT member_id 
    FROM members m 
    WHERE m.score = MAX(members.score) 
  ) AS member
FROM members
GROUP BY group_id