By: Joe Gavin |Updated: 2023-04-10 |Comments (2) | Related: > TSQL
Problem
The SQL language can be very powerful in helping you manipulate data and one part of SQLthat can be super helpful is ordering results in a specific order. In this SQLtutorial, we will look at different ways you can use SQL to order data along withseveral examples in a Microsoft SQL Server database.
Solution
Sorting result sets is done using the SQL ORDER BY clause in a SELECT statement.ORDER BY, as the name implies, orders a result set in ascending or descending order.We'll step through some examples that show how ORDER BYworks.
AdventureWorks2019 Database
For these examples, we will use the free sample databaseAdventureWorks. All the examples are queryingthe Person.Person table.
We will use the AdventureWorksLT2019 database for the examples as follows.
USE [AdventureWorks2019];GO
ORDER BY Syntax
Here is the full syntax for ORDER BY:
ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] [ <offset_fetch> ] <offset_fetch> ::= { OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [ FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY ] }SQL ORDER BY in Ascending Order
First, in the following query we'll get a list of full names of employees(WHERE clause) and order the list byLastName in ascending sort order.
SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName];GO
The ORDER BY default is ascending order, and specifying it is optional.
Here we specify ascending order, but it willwork the same way with or without using ASC.
SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName] ASC;GO
SQL ORDER BY Using Column Number
I want to preface this example by saying that just because you can do somethingdoes not mean you should.
It's valid to sort a result set on a column by usingthe column number based on its position in the SELECT statement. LastName is thefirst column, so it is number 1. ORDER BY 1 still orders the results by LastName.However, this is messy, tougher to read, and requires changing if the SELECT changes.It's definitely not a good practice.
SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY 1;GO
This will yield the same results as the previous two queries.
By not specifying the ascending or descending, the results will use thedefault of ascending order.
SQL ORDER BY in Descending Order
What if we want the result set sorted in descending order? As we saw, the firstthree examples are all ordering by the LastName column in ascending order. To returnthe results by LastName in descending order, simply specify DESCkeyword after ORDER BYLastName as shown with this SQL statement.
SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName] DESC;GO
SQL ORDER BY on Multiple Columns
So far, our result sets have been sorted by only one column. We're notlimited to that.
Here, we sort by LastName first, then FirstName second. There arethree employees with the last name 'Brown'. Their first names, 'Eric, 'Jo',and 'Kevin', are sorted in ascending order.
SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName], [FirstName];GO
SQL ORDER BY on Multiple Columns in Ascending and Descending Order
We can also sort by multiple columns and mix ascending and descending orders.To sort by LastName in ascending order and FirstName in descending order, we simplyput a 'DESC' after FirstName. Now we have the first names in the order: 'Kevin,'Jo',and 'Eric'.
SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName] ASC, [FirstName] DESC;GO
Specify Number of Records to Return with ORDER BY
To specify the number of sorted records to return, we can use the TOP clausein a SELECT statement along with ORDER BY to give us the first x number of recordsin the result set.
This query will sort by LastName and return the first 25 records.
SELECT TOP 25 [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName] ASC;GO
Limit Number of Rows Returned with ORDER BY
The OFFSET and SET clauses can also be used in an ORDER BY to limit the numberof rows returned by a query. OFFSET specifies how many rows to skip over beforestarting to return rows. For example, an OFFSET of 0 means skip 0 rows and startat the first row. FETCH optionally specifies how many rows to return.
This query says return the first 25 records of the employees' LastName,FirstName, and MiddleName starting at the first record:
DECLARE @PageNumber INT = 0DECLARE @RowsOfPage INT = 25 SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName] ASC, [FirstName] ASC OFFSET @PageNumber ROWSFETCH NEXT @RowsOfPage ROWS ONLY;GO
Changing the OFFSET to 25 will give us the next 25 results.
DECLARE @PageNumber INT = 25DECLARE @RowsOfPage INT = 25 SELECT [LastName], [FirstName], [MiddleName]FROM [Person].[Person]WHERE [PersonType] = 'EM'ORDER BY [LastName] ASC, [FirstName] ASC OFFSET @PageNumber ROWSFETCH NEXT @RowsOfPage ROWS ONLY;GO
The advantage of using OFFSET and FETCH is that it makes it possible to pagethrough a result set, just as we would see in a Google search. Here, we'llreturn all the records, 25 at a time in one query:
DECLARE @PageNumber INTDECLARE @RowsOfPage INTDECLARE @MaxTablePage FLOAT SET @PageNumber = 1SET @RowsOfPage = 25SELECT @MaxTablePage = COUNT(*)FROM [Person].[Person]WHERE [PersonType] = 'EM'SET @MaxTablePage = CEILING(@MaxTablePage / @RowsOfPage) WHILE @MaxTablePage >= @PageNumberBEGIN SELECT [LastName], [FirstName], [MiddleName] FROM [Person].[Person] WHERE [PersonType] = 'EM' ORDER BY [LastName] ASC, [FirstName] ASC OFFSET (@PageNumber - 1) * @RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY SET @PageNumber = @PageNumber + 1END;GO
Next Steps
Here are some MSSQLTips with more examples of using the ORDER BY clause:
- SELECT with ORDER BY
- SQL ORDER BY Clause
- SQL ORDER BY Clause Examples
- SQL WHERE IS NOT NULL for SELECT, INSERT, UPDATE and DELETE
- Advanced Use of the SQL Server ORDER BY Clause
- SQL Server SELECT Examples
- SQL Queries Tutorial
- Avoid ORDER BY in SQL Server views
- Building SQL Server Indexes in Ascending vs Descending Order
- Execute Dynamic SQL commands in SQL Server
- SQL Server Cursor Example
About the author
Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
