SQL Server ORDER BY

Summary: in this tutorial, you will learn how to use the SQL Server ORDER BY clause to sort the result set of a query by one or more columns.

Introduction to the SQL Server ORDER BY clause

When using the SELECT statement to query data from a table, the order of rows in the result set is not specified. This means that SQL Server can return a result set with rows in an unspecified order.

The only way to guarantee that the rows in the result set are sorted is to use the ORDER BY clause.

Here’s the syntax of the ORDER BY clause:

SELECT
    select_list
FROM
    table_name
ORDER BY 
    column_name | expression [ASC | DESC ];Code language: SQL (Structured Query Language) (sql)

In this syntax:

column_name | expression

First, you specify a column name or an expression by which to sort the result set of the query. If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted further by the second column, and so on.

The columns that appear in the ORDER BY clause must correspond to either column in the select list or columns defined in the table specified in the FROM clause.

ASC | DESC

Second, use ASC or DESC to specify whether the values in the specified column should be sorted in ascending or descending order.

The ASC keyword sorts the result from the lowest value to the highest value, while the DESC keyword sorts the result set from the highest value to the lowest one.

If you don’t explicitly specify ASC or DESC, SQL Server uses ASC as the default sort order. Additionally, SQL Server treats NULL as the lowest value.

When processing the SELECT statement that has an ORDER BY clause, the SQL Server processes the ORDER BY clause last.

SQL Server ORDER BY clause example

We will use the customers table in the sample database from the demonstration.

SQL Server Order By - customers table

1) Sort a result set by one column in ascending order

The following statement sorts the customer list by the first name in ascending order:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    first_name;
Code language: SQL (Structured Query Language) (sql)
SQL Server ORDER BY - sort by one column

In this example, because we did not specify ASC or DESC, the ORDER BY clause used ASC by default.

2) Sort a result set by one column in descending order

The following statement sorts the customer list by the first name in descending order.

SELECT
	firstname,
	lastname
FROM
	sales.customers
ORDER BY
	first_name DESC;Code language: SQL (Structured Query Language) (sql)
SQL Server ORDER BY - sort by one column in descending order

In this example, because we specified the DESC explicitly, the ORDER BY clause sorted the result set by values in the first_name column in descending order.

3) Sort a result set by multiple columns

The following statement retrieves the first name, last name, and city of the customers. It sorts the customer list by the city first and then by the first name.

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city,
    first_name;Code language: SQL (Structured Query Language) (sql)
SQL Server ORDER BY - sort by two columns

4) Sort a result set by multiple columns and different orders

The following statement sorts the customers by the city in descending order and then sorts the sorted result set by the first name in ascending order.

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    city DESC,
    first_name ASC;Code language: SQL (Structured Query Language) (sql)
SQL Server ORDER BY - sort by two columns in differnt orders

5) Sort a result set by a column that is not in the select list

It is possible to sort the result set by a column that does not appear on the select list. For example, the following statement sorts the customer by the state even though the state column does not appear on the select list.

SELECT
    city,
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    state;Code language: SQL (Structured Query Language) (sql)
SQL Server ORDER BY - sort by hidden column

Note that the state column is defined in the  customers table. If it was not, then you would have an invalid query.

6) Sort a result set by an expression

The LEN() function returns the number of characters in a string. The following statement uses the LEN() function in the ORDER BY clause to retrieve a customer list sorted by the length of the first name:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    LEN(first_name) DESC;
Code language: SQL (Structured Query Language) (sql)
SQL Server ORDER BY - sort by an expression

7) Sort by ordinal positions of columns

SQL Server allows you to sort the result set based on the ordinal positions of columns that appear in the select list.

The following statement sorts the customers by first name and last name. But instead of specifying the column names explicitly, it uses the ordinal positions of the columns:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    1,
    2;
Code language: SQL (Structured Query Language) (sql)

In this example, 1 means the first_name column, and 2 means the last_name column.

Using the ordinal positions of columns in the ORDER BY clause is considered a bad programming practice for a couple of reasons.

  • First, the columns in a table don’t have ordinal positions and need to be referenced by name.
  • Second, when you modify the select list, you may forget to make the corresponding changes in the ORDER BY clause.

Therefore, it is a good practice to always specify the column names explicitly in the ORDER BY clause.

Summary

  • Use the SQL Server ORDER BY clause to sort a result set by columns in ascending or descending order.
  • Use the ASC keyword to sort rows by a column in ascending order.
  • Use the DESC keyword to sort rows by a column in descending order.
Was this tutorial helpful?