Ordering and grouping results in MySQL

In MySQL when you are fetching a result set and you need to order a result set that is grouped by a column, you might notice some unexpected behaviour. Take the following dataset:

person_id	score
1	100
2	120
2	180
1	150
3	200
3	150
2	140
1	130

Now we have the simple task: “Get the max score of each person”. Intuitively, you might try something like this:

SELECT * FROM my_table GROUP BY person_id ORDER BY score DESC;

1	SELECT * FROM my_table GROUP BY person_id ORDER BY score DESC;

It might surprise you but you won’t get the expected results from this:

person_id	score
3	200
2	120
1	100

Now what kind of sorcery is this?!? The person with ID 3 seems to be correct, since he has a max score of 200, but person 1&2 are completely off!

What happens here?

Believe it or not: the result is actually quite logical: you first use a GROUP BY method, which tells MySQL to group it’s records by person_id . In this case, grouping means nothing more than ignoring every other row that happens to have the same person_id . So that means that all the latter entries, with higher scores, are ignored.
Now when the ORDER BY is executed, it orders nothing more than our grouped result set. Unfortunately, these 2 methods cannot be swapped around. So how can we do the ordering before the grouping?

Nested queries

The solution is fairly simple. Just nest the query:

SELECT * FROM 
  (
    SELECT * FROM my_table ORDER BY score DESC
  ) AS t 
GROUP BY person_id;

SELECT * FROM

(

SELECT * FROM my_table ORDER BY score DESC

) AS t

GROUP BY person_id;

With this nested query, we create a temporary result set which is complete and ordered by score. We store this result set in an alias called t , since MySQL requires this. And finally we can group this (ordered) result by person_id . The result:

person_id	score
1	150
2	180
3	200

Visitors give this article an average rating of 5.0 out of 5.

How would you rate this article?

★ ★ ★ ★ ★

One thought on “Ordering and grouping results in MySQL”

Marco Bax says:

July 31, 2015 at 10:54

Or use MAX(), which is way faster than subqueries. 🙂

SELECT
mt.person_id,
MAX(mt.score)
AS highest_score
FROM my_table
AS mt
GROUP BY mt.person_id ASC
ORDER BY highest_score
DESC

Reply