SQL ORDER BY Clause

By:   |   Updated: 2021-05-26   |   Comments (2)   |   Related: > TSQL


Problem

Even if you have been a SQL database professional for many years and have been writing and analyzing T-SQL queries for the bulk of that time, I can almost guarantee that you still don't know every option that exists for even the simplest, most basic clauses. I might have tried to argue with that point before sitting down to start working on this tutorial where we will look at all of the options for the ORDER BY clause. As I found out, there are some interesting options for this clause that I was not aware of that can come in handy in certain situations. Hopefully, after reading through the following tip, you will also learn something new just as I did while researching and writing it.

Solution

All database professionals have seen the ORDER BY clause and know that it is used in a SQL SELECT statement to specify the order in which rows are returned for a given query's result set. One important thing to note is that if an ORDER BY clause is not specified in the SQL SELECT statement then there is no guarantee that the order the rows will be returned in for your result set will be the same from call to call for the same query.

The syntax for the ORDER BY clause is very straightforward and is basically the keyword "ORDER BY" followed by an expression. This expression can be a single column, column list or any valid SQL expression. The columns can be referenced by name, alias or integer representing the column position from the SELECT list. The below image is taken from the Microsoft documentation shows the syntax for the ORDER BY clause and for the rest of this tip we will go through examples of each and every option available.

order by syntax

Note: All tests were run against AdventureWorks2017 sample database using SQL Server 15.0.2080.9.

SQL ORDER BY Clause with Single Column

For our first example we will look at the most basic use case of the ORDER BY clause, ordering by a single column. The below example will return the list of employees ordered by their HireDate. 

SELECT [JobTitle],[FirstName],[LastName],[HireDate]
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P ON E.BusinessEntityID=P.BusinessEntityID
ORDER BY [HireDate];
query results

SQL ORDER BY Clause with Multi-Column

Next let’s look at adding a second column to the clause in the following SQL statement. In the below example we will add the LastName column so that anyone with the same HireDate will then be sorted by their LastName.

SELECT [JobTitle],[FirstName],[LastName],[HireDate]
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P ON E.BusinessEntityID=P.BusinessEntityID
ORDER BY [HireDate],[LastName];
query results

SQL ORDER BY Clause with ASC | DESC

You might have noticed in our first two examples everything was sorted in ascending order. That is the default setting. If you want to sort in the opposite order, you can specify the DESC clause. Note that this clause can be applied to each column so that one column is sorted in ascending order and another column is sorted in descending order. An example of this is shown below. Notice that all we changed is that we will now sort the LastName column in descending order.

SELECT [JobTitle],[FirstName],[LastName],[HireDate]
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P ON E.BusinessEntityID=P.BusinessEntityID
ORDER BY [HireDate] ASC,[LastName] DESC;
query results

SQL ORDER BY Clause with Collation

Another thing that can affect the way your result set is sorted is the collation setting for the database. For those that aren’t familiar with this, the collation is a set of rules that are used to determine how data is sorted and compared. These settings only apply to char, varchar, nchar, and nvarchar typed columns. Before we get into an example of this let’s first check our current collation settings. It’s important to note here that although in most cases the collation will be set the same for everything on the server, this setting can be set at the server, database or table/column level.

To check at the server and database level you can use the following statements. 

SELECT SERVERPROPERTY('collation')
SELECT name, collation_name FROM sys.databases
WHERE name like 'AdventureWorks2017';
query results

To check the table/column level settings you can use the following statement. 

SELECT t.name TableName, c.name ColumnName, collation_name  
FROM sys.columns c  
INNER JOIN sys.tables t on c.object_id = t.object_id;
query results

Now that we know how to check the settings let’s take a look at an example of how we can alter this behavior using the ORDER BY clause. For this example, we will use the fact that the default collation is case-insensitive and show that if we change the collation to a case sensitive one, we get a different sort order.

Note: To make this work we need to update an entry in the Person table so their name starts with a lower-case letter.

UPDATE [Person].[Person] SET [FirstName] ='andrew' WHERE [BusinessEntityID] IN (47);

Running the following query and sorting by FirstName we get the result below. This is with the default SQL_Latin1_General_CP1_CI_AS case-insensitive collation. 

SELECT [JobTitle],[FirstName],[LastName],[HireDate]
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P ON E.BusinessEntityID=P.BusinessEntityID
WHERE [FirstName] LIKE 'Andrew'
ORDER BY [FirstName];
query results

Let’s add the COLLATE option to the ORDER BY clause and specify a case-sensitive collation, SQL_Latin1_General_CP1_CS_AS.

SELECT [JobTitle],[FirstName],[LastName],[HireDate]
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P ON E.BusinessEntityID=P.BusinessEntityID
WHERE [FirstName] LIKE 'Andrew'
ORDER BY [FirstName] COLLATE SQL_Latin1_General_CP1_CS_AS;

You can see with this result set that the lower-case FirstName is now returned first.

query results

SQL ORDER BY Clause with Using Aliases or Column Position

As was mentioned in the syntax section, the columns in the ORDER BY clause can also be referenced by using the column alias or by the integer position of the column in the select list. Looking at this next example, the second column in the result set is defined using the alias FullName (concatenation of LastName, FirstName). We can now reference this FullName alias in the ORDER BY clause to sort by this new column.

SELECT [JobTitle],[LastName] + ', ' + [FirstName] AS FullName
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P ON E.BusinessEntityID=P.BusinessEntityID
ORDER BY FullName;
query results

Similarly, if we didn’t want to use the alias, we could use the fact that this new column is the second column in the select list and reference it as such in the ORDER BY clause and we get the same result.

SELECT [JobTitle],[LastName] + ', ' + [FirstName] AS FullName
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P ON E.BusinessEntityID=P.BusinessEntityID
ORDER BY 2;
query results

Keep in mind that when using column position in the ORDER BY clause you really have to be careful when columns in the select list are added or removed as this could affect their position. I always recommend using the column name or alias in the ORDER BY clause because of this.

SQL ORDER BY Clause Handling NULLS

SQL Server treats NULL values as the lowest possible value for a given datatype. This means that if you specify a column in the ORDER BY clause that has null values in ascending order the NULL values will appear first in the result set. Conversely, if you specify descending order, they will appear last in the result set. Here is a simple example of each case.

SELECT [Name],[ProductNumber],[Color]
FROM [Production].[Product]
ORDER BY [Color] ASC;
query results 
SELECT [Name],[ProductNumber],[Color]
FROM [Production].[Product]
ORDER BY [Color] DESC;
query results

Unfortunately, SQL Server does not support the NULLS FIRST/LAST option as some other relational database platforms do but there are some tricks you can use to overcome this if you do want your result set to have ascending order but with NULL values last.

  • Use COALESCE and if NULL set column value in ORDER BY clause to maximum column value
  • Use CASE statement with an extra dummy column in ORDER BY to first sort by NULL/NON NULL

SQL ORDER BY Clause Using Expressions

Expressions can also be used within the ORDER BY clause and to illustrate this we will build on one of the last points above and show how we can use the COALESCE function to move NULL values to the end of the result set when sorting in ascending order. In the following example we will use COALESCE function to covert the column in the ORDER BY clause to 'ZZZZZZZZZZ' if the Color column is NULL which will put it at the end of the result set. Here is the TSQL to accomplish this.

SELECT [Name],[ProductNumber],[Color]
FROM [Production].[Product]
ORDER BY COALESCE([Color],'ZZZZZZZZZZ') ASC;
query results

We can see above that the products with a valid Color are returned first and if we scroll down in the result set the NULL values in the Color column now appear at the end of the result set.

query results

SQL ORDER BY Clause Using the TOP clause

While not explicitly part of the ORDER BY clause the TOP clause comes in handy when used in conjunction with this clause. It’s not often, and definitely not recommended, to use the TOP clause without the ORDER BY clause since without it there would be no guarantee as to which rows would be returned when you add the TOP clause. As TOP would suggest it limits the number of rows returned in your result set to whatever number you specify. In the example below we only want to see the 10 most expensive products and to do this we can order by ListPrice descending and limit the result set to 10 using the TOP clause.

SELECT TOP 10 [ProductID],[Name],[ListPrice]
FROM [Production].[Product]
ORDER BY [ListPrice] DESC;
query results

SQL ORDER BY Clause with OFFSET and FETCH clause

In this next example we will look at the OFFSET and FETCH options of the ORDER BY clause. These options are usually used to perform some sort of server-side paging with query results. The OFFSET clause allows you to specify the number of rows in the result set to skip before rows are returned to the client. The FETCH clause then specifies how many rows to return to the client after the OFFSET clause has been processed. In the following statement we can assume that someone is requesting order details and they are on the 6th page displaying 10 records per page.

SELECT [SalesOrderID],[RevisionNumber],[OrderDate],[DueDate],[ShipDate],[Status]
FROM [Sales].[SalesOrderHeader]
ORDER BY [OrderDate]
OFFSET 50 ROWS
FETCH NEXT 10 ROWS ONLY;
query results

SQL ORDER BY Clause with Ranking Functions

In our last example we will take a look at how the ORDER BY clause is used in ranking or window functions for a SELECT query. The syntax for all four of these functions, ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE(), is the same so we will just take a look at one example, RANK(). To illustrate how ORDER BY is used for this function we will calculate the top sales person by region so in this case the window function will partition by TerritoryID and sort by SalesYTD from highest to lowest. Here is the TSQL for this use case.

SELECT p.FirstName, p.LastName, st.Name
    ,RANK() OVER (PARTITION BY st.TerritoryID ORDER BY s.SalesYTD DESC) AS "Rank"  
    ,s.SalesYTD 
FROM Sales.SalesPerson AS s   
INNER JOIN Person.Person AS p   
      ON s.BusinessEntityID = p.BusinessEntityID  
INNER JOIN Person.Address AS a   
      ON a.AddressID = p.BusinessEntityID  
INNER JOIN Sales.SalesTerritory AS st
      ON s.TerritoryID = st.TerritoryID
WHERE st.TerritoryID IS NOT NULL AND s.SalesYTD <> 0;
query results
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

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

View all my tips


Article Last Updated: 2021-05-26

Comments For This Article




Tuesday, June 1, 2021 - 1:12:20 PM - Joe F Celko Back To Top (88786)
The official fiction in the ANSI/ISO standards is that an ORDER BY clause converts the result of the query from an unordered set into an explicit cursor. There is a subtle difference here between changing the structure of the result and still thinking of it as "some kind of file" instead. Also, the NULLS FIRST/LAST has been a part of the standard for many years now; Microsoft is just behind the curve. In the event of a tie, sorted order is not specified. This means we endorsed non-stable sorting, if you remember your freshman computer science class.

Wednesday, May 26, 2021 - 10:43:48 AM - david smith Back To Top (88745)
-- Good article will use offset now.
-- And my favorite, the case statement to sort on SPECIFIC values in the column
SELECT name FROM sys.databases
ORDER BY
CASE WHEN name IS NULL THEN 0
WHEN name LIKE 'staging%' THEN 1 -- show staging databases first
WHEN name LIKE '%test%' THEN 3 -- show temporary database last
ELSE 2 -- Everything else in the middle
END,
name -- within the 1,2,3 still sort by name














get free sql tips
agree to terms