GROUP BY groups rows based on one or more column and produces grouped rows. 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 DESCCode language: SQL (Structured Query Language) (sql)
This produces the following results, showing that it sorted the groups by date in descending order:
Example – ORDER BY group’s aggregate value
When you use an aggregate function (COUNT/SUM/MIN/MAX/AVG) with GROUP BY, 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 DESCCode 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:
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) DESCCode 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.