SQL ORDER BY Examples to Sort and Order Data

Problem

The SQL language can be very powerful in helping you manipulate data and one part of SQL that can be super helpful is ordering results in a specific order. In this SQL tutorial, we will look at different ways you can use SQL to order data along with several 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 BY works.

AdventureWorks2019 Database

For these examples, we will use the free sample database AdventureWorks. All the examples are querying the 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 by LastName 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 will work 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 something does not mean you should.

It’s valid to sort a result set on a column by using the column number based on its position in the SELECT statement. LastName is the first 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 the default of ascending order.

SQL ORDER BY in Descending Order

What if we want the result set sorted in descending order? As we saw, the first three examples are all ordering by the LastName column in ascending order. To return the results by LastName in descending order, simply specify DESC keyword after ORDER BY LastName 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 not limited to that.

Here, we sort by LastName first, then FirstName second. There are three 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 simply put 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

1 columns in ascending and 1 in descending order

Specify Number of Records to Return with ORDER BY

To specify the number of sorted records to return, we can use the TOP clause in a SELECT statement along with ORDER BY to give us the first x number of records in 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 number of rows returned by a query. OFFSET specifies how many rows to skip over before starting to return rows. For example, an OFFSET of 0 means skip 0 rows and start at 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 = 0
DECLARE @RowsOfPage INT = 25
 
SELECT [LastName],
       [FirstName],
       [MiddleName]
FROM [Person].[Person]
WHERE [PersonType] = 'EM'
ORDER BY [LastName] ASC, [FirstName] ASC 
OFFSET @PageNumber ROWS
FETCH NEXT @RowsOfPage ROWS ONLY;
GO

Changing the OFFSET to 25 will give us the next 25 results.

DECLARE @PageNumber INT = 25
DECLARE @RowsOfPage INT = 25
 
SELECT [LastName],
       [FirstName],
       [MiddleName]
FROM [Person].[Person]
WHERE [PersonType] = 'EM'
ORDER BY [LastName] ASC, [FirstName] ASC 
OFFSET @PageNumber ROWS
FETCH NEXT @RowsOfPage ROWS ONLY;
GO

The advantage of using OFFSET and FETCH is that it makes it possible to page through a result set, just as we would see in a Google search. Here, we’ll return all the records, 25 at a time in one query:

DECLARE @PageNumber INT
DECLARE @RowsOfPage INT
DECLARE @MaxTablePage FLOAT
 
SET @PageNumber = 1
SET @RowsOfPage = 25
SELECT @MaxTablePage = COUNT(*)
FROM [Person].[Person]
WHERE [PersonType] = 'EM'
SET @MaxTablePage = CEILING(@MaxTablePage / @RowsOfPage)
 
WHILE @MaxTablePage >= @PageNumber
BEGIN
    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 + 1
END;
GO

Next Steps

Here are some MSSQLTips with more examples of using the ORDER BY clause:

Resources

Community

Subscribe