·

photo credit: BLUE SKY WITH CONFETTI via photopin (license)

Ordering and grouping results in MySQL

Tags: ,

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:

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:

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”

  1. Marco Bax says:

    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

Leave a Reply