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

• You Old Fool: thank you, you've answered the "is there a workaround" part of my question, but I'd really like to know why we are not allowed to do this in one pass? MAX(score) can clearly be retrieved but not compared against and this limitation is a bit mysterious to me.
• Rick James: @billynoah - Another possible answer is that SQL is nearly half a century old; it was really advanced for the time; you are asking for something even more advanced.
• You Old Fool: I appreciate your input Rick and and putting things in historical context is helpful, but I'm looking for more of an explanation about the limitation here. It doesn't have to be amazingly in depth, but should be a bit more than "Things don't work that way".

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

• You Old Fool: this is a cool trick but doesn't answer my question. the problem as I've tried to convey is that score isn't really a value in the table - it's the result of a big complex query. So doing it your way (or the way I'm doing it in real life) is not so simple as querying the table members - it would require reproducing the entire subquery, which is essentially what RickJames has told me above, is unavoidable. thanks anyway for your effort