ORDER BY after GROUP BY does not working

Question

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