ORDER BY after GROUP BY does not working
Solution 1
Let's look at what you are doing step by step:
SELECT id, user, MAX(score) FROM table_1 GROUP BY user
Here you are grouping by user name, so you get one result row per user name. In this result row you select the user name, the maximum score found for this user name (which is 16 for 'mike') and one of the IDs found for the user name (which can be 1 or 3 for 'mike', the DBMS is free to choose one). This is probably not what you want.
SELECT * FROM (...) AS sub ORDER BY 'sub.score' ASC;
'sub.score' is a string (single quotes). You want to order by the max score from your subquery instead. So first give the max(score)
a name, e.g. max(score) as max_score
, and then access that: ORDER BY sub.max_score ASC
.
Anyway, if you want the record with the maximum score for a user name (so as to get the according ID, too), you could look for records for which not exists a record with the same user name and a higher score. Sorting is easy then: as there is no aggregation, you simply order by score:
select * from table_1 t1 where not exists
(select * from table_1 higher where higher.name = t1.name and higher.score > t1.score)
order by score;
Solution 2
Assuming user|score is unique..:
SELECT x.*
FROM table_1 x
JOIN ( SELECT user, MAX(score) score FROM table_1 GROUP BY user) y
ON y.user = x.user
AND y.score = x.score
ORDER BY x.score
Solution 3
No need to write sub queries. Simply you can use this way:
SELECT id, `user`, MAX(score) FROM table_1 GROUP BY `user`
ORDER BY MAX(score);
If you want query with sub query:
SELECT * FROM (SELECT id, `user`, MAX(score) as max_score FROM table_1
GROUP BY `user`) AS sub ORDER BY max_score;
Northumber
Updated on June 16, 2022Comments
-
Northumber about 2 years
SELECT * FROM (SELECT id, user, MAX(score) FROM table_1 GROUP BY user) AS sub ORDER BY 'sub.score' ASC;
This SQL query should select from a table only a score per user, and for accuracy, the highest.
The table structure is this:
+-----------------------+ | id | score | username | +-----------------------+ | 1 | 15 | mike | | 2 | 23 | tom | | 3 | 16 | mike | | 4 | 22 | jack | etc..
The result should be like:
3 mike 16 2 tom 23 4 jack 22
And then reordered:
3 mike 16 4 jack 22 2 tom 23
But the query does not reorder the subquery by score. How to do so?