SQL ORDER BY Clause Code Examples (2024)

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 ORDERBY should be the keywords I need. How does ORDER BY work? What things can I sort on? Does this affect performance? Are there anybest practices regarding ORDER BY that I should follow?

Solution

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

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

SQL ORDER BY Clause Basics

The ORDER BY clause of aSQL SELECT statement goes at the end. It includesthe keywords ORDER BY followed by a series of expressions (ASC | DESC) on which the data shouldbe 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 mixand match different kinds of expressions in the same ORDER BY clause. The expressionsalso do not have to be part of the query output. There will be examples ofeach of these scenarios in the demos below.

An ORDER BY clause can have an unlimited number of expressions, but will be valideven with a singleton. When there are multiple expressions in an ORDER BYclause 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 eachexpression the additional keyword ASC or DESC will tell SQL Server to sort thatexpression ascending or descending respectively. Ascending is the defaultso 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 andnumbers are sorted smallest to largest. The sorting method for text columnsis defined by the databaseCOLLATION level. For databases using the English language, thisusually means that text columns are sorted alphabeticallyas the most common and default COLLATION levels sort this way.

SQL ORDER BY with Existing Columns

Consider this very simpleSELECT statementin the following query:

SELECT * FROM Sales.Orders;

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

Changing the query slightly to include an ORDER BY clause and the expression"SalespersonPersonIDASC" changes the output by putting all of the rows in order by salesperson(column name), startingwith 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 includedand will not be included on any more scripts in this tutorial.

SELECT * FROM Sales.OrdersORDER BY SalespersonPersonID ASC;

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

SELECT * FROM Sales.OrdersORDER BY SalespersonPersonID DESC;

SQL ORDER BY Calculated Columns

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

There is also a second expression, this one sorting by StockItemID in descendingorder. 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 , StockItemIDFROM Sales.InvoiceLinesORDER BY PreTaxAmt, StockItemID DESC;

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

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

SELECT SalespersonPersonID, COUNT(*) as OrderCountFROM Sales.OrdersWHERE OrderDate BETWEEN '1/1/2013' and '1/31/2013'GROUP BY SalespersonPersonIDORDER BY OrderCount DESC;

The leading salesperson will be pushed to the top of this query. The problemhere is that the business really only wants to see the top salesperson and not allof 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 determinewhich one salesperson had the best month. This is accomplished by adding aTOP clause to the beginning of the statement. It is a best practice that whenusing a TOP clause to always include an ORDER BY in conjunction. Using TOPwithout an ORDER BY can lead to unexpected results since the sort order cannot beguaranteed.

SELECT TOP 1 SalespersonPersonID, COUNT(*) as OrderCountFROM Sales.OrdersWHERE OrderDate BETWEEN '1/1/2013' and '1/31/2013'GROUP BY SalespersonPersonIDORDER 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 logicalfirst step would be to simply remove the COUNT(*) from the SELECT part of the querylimiting the output to the SalespersonPersonID. But making this change bringsabout an error because the ORDER BY clause is currently referencing a column aliasthat was defined in the SELECT statement that was just removed.

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

SELECT TOP 1 SalespersonPersonIDFROM Sales.OrdersWHERE OrderDate BETWEEN '1/1/2013' and '1/31/2013'GROUP BY SalespersonPersonIDORDER 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 sortingby columns that are in the output of the query. SQL Server allows for an expressionto 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 number4 could be used as the argument instead of the column alias or the calculation.

SELECT InvoiceLineID, Quantity, UnitPrice, Quantity * UnitPrice as PreTaxAmt, ExtendedPrice , StockItemIDFROM Sales.InvoiceLinesORDER BY 4 DESC;

While this is a really neat shortcut when playing around in SSMS or ADS, it isnot something that should ever go to production in any way. Using column ordinalsfor sorting is a significant T-SQL anti-pattern. It is far too easy for the query to be modified by someone adding or removinga 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 thatcost depends on a few factors. How many rows need to be sorted? Is therean index that supports that sort? Was SQL Server already planning on sortingthat way?

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

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

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

If the sort of a query is negatively impacting performance, consider modifyingindexes to support the sort -- if possible -- or consider pushing the sorting operationto the client. Many reporting tools are very good at sorting result sets andwon’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

Article Last Updated: 2021-12-20