SQL Server ORDER BY Clause (2024)

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_listFROM table_nameORDER 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 theDESC keyword sortsthe 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.

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_nameFROM sales.customersORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

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.

SELECTfirstname,lastnameFROMsales.customersORDER BYfirst_name DESC;Code language: SQL (Structured Query Language) (sql)

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_nameFROM sales.customersORDER BY city, first_name;Code language: SQL (Structured Query Language) (sql)

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_nameFROM sales.customersORDER BY city DESC, first_name ASC;Code language: SQL (Structured Query Language) (sql)

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_nameFROM sales.customersORDER BY state;Code language: SQL (Structured Query Language) (sql)

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 anexpression

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_nameFROM sales.customersORDER BY LEN(first_name) DESC;Code language: SQL (Structured Query Language) (sql)

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_nameFROM sales.customersORDER 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.