When you use the select statement to query data from a table, the order of rows in the result set is not guaranteed. It means that SQL Server can return a result set with an unspecified order of rows.
The only way for you to guarantee that the rows in the result set are sorted is to use the
ORDER BY
clause. The following illustrates the ORDER BY
clause syntax:
In this syntax:
column_name
| expression
First, you specify a column name or an expression on 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 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 to columns defined in the table specified in the FROM
clause.
ASC
| DESC
Second, use
ASC
or DESC
to specify the whether the values in the specified column should be sorted in ascending or descending order.
The
ASC
sorts the result from the lowest value to the highest value while the DESC
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. Also, SQL Server treats NULL as the lowest values.
When processing the
SELECT
statement that has an ORDER BY
clause, the ORDER BY
clause is the very last clause to be processed.
SQL Server ORDER BY
clause example
We will use the
customers
table in the SAMPLE DATABASE from the demonstration.
A) Sort a result set by one column in ascending order
The following statement sorts the customer list by the first name in ascending order:

In this example, because we did not specify
ASC
or DESC
, the ORDER BY
clause used ASC
by default.B) Sort a result set by one column in descending order
The following statement sorts the customer list by the first name 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 the descending order.C) 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.
D) Sort a result set by multiple columns and different orders
The following statement sorts the customers by the city in descending order and the sort the sorted result set by the first name in ascending order.

E) 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.
Note that the
state
column is defined in the customers
table. If it was not, then you would have an invalid query.F) Sort a result set by an expression
The LEN() function returns the number of characters of 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.
G) 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:
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 as 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.
In this tutorial, you have learned how to use the SQL Server
ORDER BY
clause to sort a result set by columns in ascending or descending order.
0 Comments