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.
Was this tutorial helpful?