MySQL: Reference aggregate function in conditional select

aggregategreatest-n-per-groupMySQL

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?

Best Answer

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".

Related Question