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?