Northumber Northumber - 1 month ago 5
MySQL Question

ORDER BY after GROUP BY does not working

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?

Answer

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;
Comments