Sorting Groups

The result of a query with GROUP BY is a table like any other, and you sort it with ORDER BY. You can sort it by the column you group by, as in the previous exercise, but also by an aggregate function. Which genres have the most games?

SELECT genre, COUNT(*) AS game_count
FROM games
GROUP BY genre
ORDER BY COUNT(*) DESC;

Instead of writing the function again, you can sort by its alias: ORDER BY game_count DESC. It's shorter, and when you later change the function, you change it in one place only. At the end of this lesson, you'll learn why an alias works in ORDER BY and where it doesn't.

Look at the result, though. Adventure and Strategy have 3 games each, and five genres have 2. Here, the tied genres happen to come in reverse alphabetical order, but nothing guarantees any order: another version of the database, or the same one with more data, may return them differently. As you know from lesson 2, a second sort column decides the ties:

SELECT genre, COUNT(*) AS game_count
FROM games
GROUP BY genre
ORDER BY game_count DESC, genre;

With LIMIT, you get the top groups. The three genres with the highest average price:

SELECT genre, ROUND(AVG(price), 1) AS average_price
FROM games
GROUP BY genre
ORDER BY average_price DESC
LIMIT 3;

Typical mistake: LIMIT 1 for “the genre with the most games” when there's a tie. If two groups share the first place, LIMIT 1 shows one of them, and you never learn about the other. Before you cut the result, look at a few more rows, and always add a second sort column, so that you know which of the tied groups you get.