SQL ORDER BY Clause Examples

By: Eric Blinn   |   Updated: 2021-12-20   |   Comments   |   Related: > TSQL

Problem

I am writing some SQL and need to sort my results. It looks like ORDER BY should be the keywords I need. How does ORDER BY work? What things can I sort on? Does this affect performance? Are there any best practices regarding ORDER BY that I should follow?

Solution

This SQL tutorial will describe the ORDER BY clause for a SQL statement in detail showing many different examples and use cases.

All of the demos in this tip will be run against SQL Server 2019 and reference the WideWorldImporters sample database which can be downloaded for free from Github.

SQL ORDER BY Clause Basics

The ORDER BY clause of a SQL SELECT statement goes at the end. It includes the keywords ORDER BY followed by a series of expressions (ASC | DESC) on which the data should be sorted (Ascending Order or Descending Order).

These expressions are usually columns, but they do not have to be columns. They can also be calculations or column ordinals. It is acceptable to mix and match different kinds of expressions in the same ORDER BY clause. The expressions also do not have to be part of the query output. There will be examples of each of these scenarios in the demos below.

An ORDER BY clause can have an unlimited number of expressions, but will be valid even with a singleton. When there are multiple expressions in an ORDER BY clause then they are separated with a comma.

There is a size limit of 8,060 bytes for all of the expressions added up. It is rare to see a scenario where that upper size limit is tested as, in practice, most ORDER BY clauses are limited to 3 expressions or less.

The sort direction is set independently for each expression. After each expression the additional keyword ASC or DESC will tell SQL Server to sort that expression ascending or descending respectively. Ascending is the default so if neither the ASC or DESC keyword is included, the expression will be sorted ascending. For this reason, it is extremely rare to see the ASC keyword in practice.

When sorting, ascending means dates and times are sorted oldest to newest and numbers are sorted smallest to largest. The sorting method for text columns is defined by the database COLLATION level. For databases using the English language, this usually means that text columns are sorted alphabetically as the most common and default COLLATION levels sort this way.

SQL ORDER BY with Existing Columns

Consider this very simple SELECT statement in the following query:

SELECT * FROM Sales.Orders;

Because no sort order was defined, the output of this statement is sorted by the clustered index.

Changing the query slightly to include an ORDER BY clause and the expression "SalespersonPersonID ASC" changes the output by putting all of the rows in order by salesperson (column name), starting with salesperson 2. This query would work exactly the same without the keyword "ASC" as that is the default sort order. In practice, the ASC is almost never included and will not be included on any more scripts in this tutorial.

SELECT * FROM Sales.Orders
ORDER BY SalespersonPersonID ASC;

Changing this query to use DESC instead of ASC (or blank) will push the rows for salesperson 20 to the top and the rows for salesperson 2 to the bottom in the sorted result set. Here is the syntax:

SELECT * FROM Sales.Orders
ORDER BY SalespersonPersonID DESC;

SQL ORDER BY Calculated Columns

This query creates a calculated column called PreTaxAmt. Sorting by that column is as simple as retyping the column alias as the expression in the ORDER BY clause. Notice that there is no sort direction declared so the default direction of ascending is going to be used.

There is also a second expression, this one sorting by StockItemID in descending order. This means that any time there is a repeating value for PreTaxAmt, those rows will be sorting by StockItemID. See the following SQL statement:

SELECT InvoiceLineID
, Quantity
, UnitPrice
, Quantity * UnitPrice as PreTaxAmt
, ExtendedPrice 
, StockItemID
FROM Sales.InvoiceLines
ORDER BY PreTaxAmt, StockItemID DESC;

The lowest PreTaxAmt items are now at the top. When there are repeat PreTaxAmt values as in row 1-4, they are sorted by StockItemID. This happens again in rows 5-9 where all the rows have 3.20 for pretax amount and are sorted from 14 down to 11 in StockItemID. Adding a 3^rd expression to the ORDER BY clause would help further sort the results where the first 2 expression were the same across 2 or more rows such as in rows 1-3. The 3^rd expression would be ignored in situations where the first 2 expressions do not match a neighboring row as in row 4.

Imagine that the business has requested information about the salesperson with the most sales for a given month as specified in the WHERE clause. The query will need to calculate an order count and then sort by that order count. The query might look something like this.

SELECT SalespersonPersonID, COUNT(*) as OrderCount
FROM Sales.Orders
WHERE OrderDate BETWEEN '1/1/2013' and '1/31/2013'
GROUP BY SalespersonPersonID
ORDER BY OrderCount DESC;

The leading salesperson will be pushed to the top of this query. The problem here is that the business really only wants to see the top salesperson and not all of the others. The next section will take this query to the next level.

SQL ORDER BY with TOP

Continuing the prior example, the business only needs to see the top row to determine which one salesperson had the best month. This is accomplished by adding a TOP clause to the beginning of the statement. It is a best practice that when using a TOP clause to always include an ORDER BY in conjunction. Using TOP without an ORDER BY can lead to unexpected results since the sort order cannot be guaranteed.

SELECT TOP 1 SalespersonPersonID, COUNT(*) as OrderCount
FROM Sales.Orders
WHERE OrderDate BETWEEN '1/1/2013' and '1/31/2013'
GROUP BY SalespersonPersonID
ORDER BY OrderCount DESC;

In this example, the business may not even want to see the order count. They may just need to know which salesperson was first in the report. A logical first step would be to simply remove the COUNT(*) from the SELECT part of the query limiting the output to the SalespersonPersonID. But making this change brings about an error because the ORDER BY clause is currently referencing a column alias that was defined in the SELECT statement that was just removed.

To accomplish what the business has asked, the calculation will need to be typed into the ORDER BY clause expression as seen in the example below. Now this query is sorting by a column that does not exist in the output. This is a perfectly reasonable use case.

SELECT TOP 1 SalespersonPersonID
FROM Sales.Orders
WHERE OrderDate BETWEEN '1/1/2013' and '1/31/2013'
GROUP BY SalespersonPersonID
ORDER BY COUNT(*) DESC;

SQL ORDER BY with Ordinals

The ORDER BY expressions shown so far have either been columns, column aliases, or calculations. There is another option that can be used only when sorting by columns that are in the output of the query. SQL Server allows for an expression to simply be a number that references the column location in the select statement.

This sample query output has 6 columns.

If the query output needs to be sorted by PreTaxAmt then the number 4 could be used as the argument instead of the column alias or the calculation.

SELECT InvoiceLineID
, Quantity
, UnitPrice
, Quantity * UnitPrice as PreTaxAmt
, ExtendedPrice 
, StockItemID
FROM Sales.InvoiceLines
ORDER BY 4 DESC;

While this is a really neat shortcut when playing around in SSMS or ADS, it is not something that should ever go to production in any way. Using column ordinals for sorting is a significant T-SQL anti-pattern.  It is far too easy for the query to be modified by someone adding or removing a column and then having the sort order suddenly change without warning.

SQL ORDER BY Performance Impacts

Sorting a result set usually comes with a cost. The significance of that cost depends on a few factors. How many rows need to be sorted? Is there an index that supports that sort? Was SQL Server already planning on sorting that way?

Measuring the impact of that is quite easy. Consider this query that has a sort operation.

SELECT SL.InvoiceID, InvoiceDate, AP.FullName, SL.Description
FROM Sales.Invoices SI
  INNER JOIN Sales.InvoiceLines SL ON SI.InvoiceID = SL.InvoiceID
  INNER JOIN [Application].People AP ON SI.SalespersonPersonID = AP.PersonID
WHERE SalespersonPersonID = 8
  AND InvoiceDate BETWEEN '1/7/2013' AND '1/20/2013'
ORDER BY InvoiceDate;

Executing this query and including the execution plan yields this output. At the very end of the query plan on the top left is this sort operation that took 7% of the overall effort.

If the sort of a query is negatively impacting performance, consider modifying indexes to support the sort -- if possible -- or consider pushing the sorting operation to the client. Many reporting tools are very good at sorting result sets and won’t struggle with the request.

Final Thoughts

SQL ORDER BY is an incredibly useful tool to have in the toolbox of a T-SQL writer and hopefully this has provided some insight on how this can be used to find the lowest value, highest value, data in alphabetical order, particular order, etc.

Next Steps

• Check out these additional tips:
  • SQL ORDER BY Clause
  • SELECT with ORDER BY
  • Advanced Use of the SQL Server ORDER BY Clause
  • Avoid ORDER BY in SQL Server views
  • SQL Server 101
  • SQL Server IN Operator
  • SQL Server NOT IN Operator
  • SQL Server Not Equals Operator
  • Tuning queries with execution plans
  • Some Tricky Situations When Working with SQL Server NULLs
  • Join SQL Server tables where columns include NULL values

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips