Search code examples
mysqlsqlgreatest-n-per-group

Why can't I use this code to select the max row

I'm trying to select the row that contains the largest number and have accomplished it using this fairly simple query:

SELECT s1.score, name
FROM scores s1
JOIN (
    SELECT id, MAX(score) score
    FROM scores
    GROUP BY id
) s2 ON s1.score = s2.score

All it does (If im not wrong), is just checking if the score field is equal the the MAX(score). So why can't we just do it using one single SELECT statement ?. Something like this:

SELECT id, score
FROM scores
GROUP BY id
HAVING MAX(score) = score

*The code above does not work, I want to ask why it is not working, because its essentially doing the same thing as the previous code I posted

Also here's the data I'm working with:

Data

Solution

  • The problem in your second query is the fact that the GROUP BY clause requires all non-aggregated fields within its context. In your case you are dealing with three fields (namely "id", "score" and "MAX(score)") and you're referencing only one (namely "id") inside the GROUP BY clause.

    Fixing that would require you to add the non-aggregated "score" field inside your GROUP BY clause, as follows:

    SELECT id, score
FROM scores
GROUP BY id, score
HAVING MAX(score) = score

    Though this would lead to a wrong aggregation and output, because it would attempt to get the maximum score for each combination of (id, score).

    And if you'd attempt to remove the "score" field from both the SELECT and GROUP BY clauses, to solve the non-aggregated columns issue, as follows:

    SELECT id
FROM scores
GROUP BY id
HAVING MAX(score) = score

    Then the HAVING clause would complain as long it references the "score" field but it is found neither within the SELECT clause nor within the GROUP BY clause.

    There's really no way for you to use that kind of notation, as it either violates the full GROUP_BY mode, or it just returns the wrong output.