Using GROUP BY with ORDER BY in SQL

There are two main ways to sort groups:

1. Use ORDER BY on the grouping columns (the columns listed in the GROUP BY clause).
2. Use ORDER BY on the group’s aggregate value (e.g., COUNT, SUM, MIN, MAX, AVG).

I'll highlight how SQL Server might implicitly sort groups (and why relying on this behavior is not a good idea).

ORDER BY a Grouping Column

You can sort the groups based on one or more columns used in the GROUP BY clause.

Here’s an example where we group by ProductCategory and then sort the groups based on ProductCategory in ascending order:

SELECT ProductCategory, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductCategory
ORDER BY ProductCategory ASC

This query groups the rows by ProductCategory and then sorts them by ProductCategory in ascending order. The result would show the product categories listed alphabetically with their corresponding total sales.

ORDER BY the Group’s Aggregate Value

When using an aggregate function with GROUP BY (such as COUNT, SUM, MIN, MAX, or AVG), you can sort the groups by their aggregate values. To do this, you can assign an alias to the aggregate function and use that alias in the ORDER BY clause.

For example, How to sort groups based on the SUM() aggregate:

SELECT ProductCategory, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductCategory
ORDER BY TotalSales DESC

In this query, TotalSales is the alias for the SUM(SalesAmount) aggregate. This sorts the results by total sales, with the highest total appearing first.

Alternatively, you can use the aggregate function directly in the ORDER BY clause without an alias:

SELECT ProductCategory, SUM(SalesAmount)
FROM Sales
GROUP BY ProductCategory
ORDER BY SUM(SalesAmount) DESC

This method has the same performance as using an alias. The only difference is that using an alias can improve readability. Don’t worry; repeating the aggregate function in ORDER BY won’t cause it to execute twice.

SQL Server and Implicit Sorting of Groups

In SQL Server, the method of sorting groups depends on the number of records. SQL Server might either sort the records or use hashing to determine the groups. You can see this in the execution plans, where it may say "HASH MATCH" if hashing is used, and in this case, the groups won’t be sorted.

If SQL Server sorts the records to determine the groups, it will order them in ascending order, even if you didn’t specify ORDER BY. However, this implicit sorting behavior should not be relied upon. GROUP BY does not always sort the results in ascending order.

If you require the groups to be sorted in a specific order, always use ORDER BY explicitly.