SQL – Using GROUP BY with ORDER BY

by

GROUP BY groups rows based on one or more columns. ORDER BY sorts rows. You can use these together to sort groups. There are two options you can use for sorting groups:

  • Use ORDER BY on grouping columns (the ones you used in the GROUP BY list).
  • Use ORDER BY on the group’s aggregate value (COUNT/SUM/MIN/MAX/AVG).

I’ll show examples below. At the end, I’ll talk about how SQL Server may implicitly sort groups (and why you shouldn’t depend on this).

Example – ORDER BY a grouping column

You can use ORDER BY to sort based on one or more grouping columns. Here’s an example of grouping rows by StreamedDate and then sorting the groups by StreamedDate as well:

SELECT StreamedDate, SUM(WatchedMins) as TotalWatchedMins 
FROM Streaming
GROUP BY StreamedDate
ORDER BY StreamedDate DESC
Code language: SQL (Structured Query Language) (sql)

This produces the following results, showing that it sorted the groups by date in descending order:

Query results showing groups sorted by a grouping column

Example – ORDER BY group’s aggregate value

When you use an aggregate function with GROUP BY (COUNT/SUM/MIN/MAX/AVG) , you can sort groups based on their aggregate values. The simplest way to do this is to provide an alias (i.e. as Something) with the aggregate function and then use the alias with ORDER BY.

Here’s an example of sorting groups based on the value of the SUM() aggregate function:

SELECT StreamedDate, SUM(WatchedMins) as TotalWatchedMins 
FROM Streaming
GROUP BY StreamedDate
ORDER BY TotalWatchedMins DESC
Code language: SQL (Structured Query Language) (sql)

TotalWatchedMins is the alias and this is being used with ORDER BY. This produces the following results, showing the dates sorted by total minutes watched:

Query results showing groups sorted by an aggregate value

If you don’t want to use an alias, you can just repeat the aggregate function call. For example, notice that SUM(WatchedMins) is in the select list and the ORDER BY list:

SELECT StreamedDate, SUM(WatchedMins)
FROM Streaming
GROUP BY StreamedDate
ORDER BY SUM(WatchedMins) DESC
Code language: SQL (Structured Query Language) (sql)

Note: This has the exact same execution plan as using an alias. Using an alias is just for readability. Don’t worry, repeating the aggregate function call in ORDER BY doesn’t make it execute twice.

SQL Server may implicitly sort groups

When you use GROUP BY, SQL Server will either sort the records or use hashing for figuring out the groups. It depends on how many records there are. You can see this in the execution plans. It will say HASH MATCH if it used hashing and the groups won’t be in sorted order.

When it sorts the records for grouping purposes, the groups will come out in ascending order even if you didn’t use ORDER BY. You may think GROUP BY always sorts in ascending order, but it doesn’t. Do not assume GROUP BY will produce sorted results. If you need groups in sorted order, always use ORDER BY explicitly.

Related Articles