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:
3
Answers
It returns all persons with same score which the score is the max:
Here’s what your queries return
Neither result seems to be what you are looking for. You could clarify by posting sample data and desired outcome.
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 theGROUP BY
clause.Fixing that would require you to add the non-aggregated "score" field inside your
GROUP BY
clause, as follows: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
andGROUP BY
clauses, to solve the non-aggregated columns issue, as follows:Then the
HAVING
clause would complain as long it references the "score" field but it is found neither within theSELECT
clause nor within theGROUP 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.