{"id":174,"date":"2018-04-13T09:57:34","date_gmt":"2018-04-13T02:57:34","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=174"},"modified":"2024-09-24T20:41:14","modified_gmt":"2024-09-24T13:41:14","slug":"sql-server-order-by","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-order-by\/","title":{"rendered":"SQL Server ORDER BY"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server ORDER BY<\/code> clause to sort the result set of a query by one or more columns.<\/p>\n\n\n\n

Introduction to the SQL Server ORDER BY clause #<\/a><\/h2>\n\n\n\n

The ORDER BY<\/code> clause is an option clause of the SELECT<\/a> statement. The ORDER BY<\/code> clause allows you to sort the result set of a query by one or more columns.<\/p>\n\n\n\n

Here’s the syntax of the ORDER BY<\/code> clause:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    table_name\nORDER<\/span> BY<\/span> \n    column_name | expression [ASC<\/span> | DESC<\/span> ];<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify a column name or an expression you want to sort in the ORDER BY<\/code> clause. <\/p>\n\n\n\n
If you use multiple columns, the ORDER BY<\/code> clause will sort rows by the first column first, then sort the sorted rows by the second column, and so on. <\/p>\n\n\n\n
The columns in the ORDER BY<\/code> clause must match either column in the select list or columns defined in the table specified in the FROM<\/code> clause.<\/p>\n\n\n\n
Second, specify the sort order by using either the ASC<\/code> or DESC<\/code> keyword. <\/p>\n\n\n\n
The ASC<\/code> keyword sorts rows from low to high, while the\u00a0DESC<\/code> keyword sorts\u00a0the rows from high to low.<\/p>\n\n\n\n
Both ASC<\/code> and DESC<\/code> are optional. If you don’t explicitly specify either ASC<\/code> or DESC<\/code>, the ORDER BY<\/code> clause defaults to ASC<\/code>.<\/p>\n\n\n\n
When processing the SELECT<\/code> statement that has an ORDER BY<\/code> clause, SQL Server processes the ORDER BY<\/code> clause last.<\/p>\n\n\n\n
SQL Server ORDER BY clause example #<\/a><\/h2>\n\n\n\n
We will use the customers<\/code> table in the sample database<\/a> from the demonstration.<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
1) Sort a result set by one column in ascending order #<\/a><\/h3>\n\n\n\n
The following statement uses the ORDER BY<\/code> clause to sort customers by their first names in ascending order:<\/p>\n\n\n
SELECT<\/span>\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, we don’t specify ASC<\/code> or DESC<\/code>, the ORDER BY<\/code> clause defaults to ASC<\/code>. Therefore, the above query is equivalent to the following query:<\/p>\n\n\n
SELECT<\/span>\n    first_name<\/span>,\n    last_name<\/span>\nFROM<\/span>\n    sales<\/span>.customers<\/span>\nORDER<\/span> BY<\/span>\n    first_name<\/span> ASC<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Sort a result set by one column in descending order #<\/a><\/h3>\n\n\n\n
The following statement uses the ORDER BY<\/code> clause to sort customers by their first names in descending order:<\/p>\n\n\n
SELECT<\/span>\n    firstname,\n    lastname\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, we explicitly use the DESC<\/code> option, so the ORDER BY<\/code> clause sorts the rows by values in the first_name<\/code> column in descending order.<\/p>\n\n\n\n
3) Sort a result set by multiple columns #<\/a><\/h3>\n\n\n\n
The following statement uses the ORDER BY<\/code> clause to sort customers by cities first and then by first names:<\/p>\n\n\n
SELECT<\/span>\n    city,\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    city,\n    first_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
4) Sort a result set by multiple columns in different orders #<\/a><\/h3>\n\n\n\n
The following statement uses the ORDER BY<\/code> clause to sort customers by cities in descending order and then sort the customers by their first names in alphabetical order:<\/p>\n\n\n
SELECT<\/span>\n    city,\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    city DESC<\/span>,\n    first_name ASC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
5) Sort a result set by a column that is not in the select list #<\/a><\/h3>\n\n\n\n
SQL Server allows you to sort a result set by columns specified in a table, even if those columns do not appear in the select list. <\/p>\n\n\n\n
For example, the following statement uses the ORDER BY<\/code> clause to sort customers by states, even though the state<\/code> column does not appear on the select list:<\/p>\n\n\n
SELECT<\/span>\n    city,\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    state;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
<\/figure>\n\n\n\n
Note that the state<\/code> column is defined in the \u00a0customers<\/code> table. If it doesn’t, then you’ll have an invalid query.<\/p>\n\n\n\n
6) Sort a result set by an expression #<\/a><\/h3>\n\n\n\n
The LEN()<\/a><\/code> function returns the number of characters in a string. <\/p>\n\n\n\n
The following statement uses the LEN()<\/a><\/code> function in the ORDER BY<\/code> clause to sort customers by the lengths of their first names:<\/p>\n\n\n
SELECT<\/span>\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    LEN<\/span>(first_name) DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
7) Sort by ordinal positions of columns #<\/a><\/h3>\n\n\n\n
SQL Server allows you to sort the result set based on the ordinal positions of columns<\/em> that appear in the select list.<\/p>\n\n\n\n
The following statement sorts the customers by first and last names. But instead of specifying the column names explicitly, it uses the ordinal positions of the columns:<\/p>\n\n\n
SELECT<\/span>\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    1<\/span>,\n    2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the numbers 1 and 2, which appear after the ORDER BY<\/code> clause, denote the first_name<\/code>  and last_name<\/code> columns respectively.<\/p>\n\n\n\n
Using the ordinal positions of columns in the ORDER BY<\/code> clause is not recommended for several reasons:<\/p>\n\n\n\n
Additionally, SQL Server treats NULL<\/a> as the lowest value. This means that it will place NULL<\/code> before other values after sorting.<\/p>\n\n\n\n