SQL Server ORDER BY

An ORDER BY clause is used to arrange the table's data in ascending or descending order based on the given column or list of columns. It is usually used with the SELECT statement as it returns the records without sorting in specific orders. It means SQL Server produces the output in an unspecified order. However, using the ORDER BY clause guarantees that the records in the output are sorted in ascending or descending order. This clause allows sorting of the table by defining single or multiple columns.

Syntax

The following are the syntax that illustrates the ORDER BY clause in SQL Server:

SELECT column_lists 
FROM table_name 
WHERE conditions  
ORDER BY column_name [ASC | DESC];

The above syntax parameter descriptions are given below:

Column_lists: It indicates the name of the columns that we want to retrieve in the result set.

Table_name: It indicates the name of a table from where we want to retrieve columns. We must provide at least one table name in the FROM clause.

Conditions: It is an optional parameter that will be used to filter the table records.

column_name [ASC | DESC]: It indicates the name of columns to which table is sorted. The ASC keyword is optional, but if defined, it sorts the output in ascending order (lowest to highest). The DESC keyword is optional, but if defined, it sorts the output in descending order (highest to lowest).

If we do not define ASC or DESC keyword, SQL Server will use ASC as the default sorting order. SQL Server considers the NULL value to be the lowest. In addition, the ORDER BY clause is the very last clause to be considered when processing a SELECT statement with an ORDER BY clause.

Example of ORDER BY clause

Let us understand how the ORDER BY clause works in the SQL Server by taking several examples practically. Here we are going to take a table employee_info for demonstration of this clause. The table employee_info contains the following records:

Example1: The following statement explains the ORDER BY clause without specifying any sorting order. Here we will specify the 'salary' column to sort the table.

SELECT name, occupation, salary
FROM employee_info
ORDER BY salary;

Executing the statement will return the below output where we see that table is sorted based on the salary column. Since we have not specified the sorting order, SQL Server will sort the table in the default order:

Example2: This example will sort the result set based on the single column in descending order. The below statement sorts the employee_info table in descending order by using the employee name column:

SELECT name, occupation, salary
FROM employee_info
ORDER BY name DESC;

Executing the statement will display the below output. Since we specified the DESC keyword explicitly, the SQL Server sorted the result set based on the name values in descending order.

Example3: This example will sort the result set based on the multiple columns. The following statement will return the name, occupation, and salary of the employees. It first sorts the employee list by the salary column and then by the name column.

SELECT name, occupation, salary
FROM employee_info
ORDER BY salary, name;

Executing the statement will produce the following result set:

Example4: This example will sort a result set based on the multiple columns and different orders. The following statement will return the name, occupation, and salary of the employees. It first sorts the employee list by the salary column in descending order and then sorts the sorted result set by the name in ascending order.

SELECT name, occupation, salary
FROM employee_info
ORDER BY 
salary DESC,
name ASC;

Executing the statement will produce the following result set:

Example5: This example will sort a result based on the column that is not defined in the SELECT list. Because, in some cases, the column name used for sorting the result does not appear on the select list. The following statement sorts the employee based on the working hour even though the working_hours column is not listed on the select list.

SELECT name, occupation, salary
FROM employee_info
ORDER BY working_hours;

It should be noted that the working hour column is defined in the employee_info table. Otherwise, we would have an invalid query. Now, execute the query to display the result:

Example6: This example will sort a result set based on the defined expression. The following example uses the LEN() function that returns the number of characters of a string. Here, we use this function in the ORDER BY clause to retrieve an employee's information sorted by the length of their name.

SELECT name, occupation, salary
FROM employee_info
ORDER BY LEN(name) DESC;

This query will produce the below output:

Example7: This example will sort the table based on the ordinal positions of columns. We do this as SQL Server allows us to sort the result by column's ordinal positions in the SELECT list.

The given query sorts the employee_info table name by specifying the name and occupation column's ordinal position instead of specifying the column names explicitly:

SELECT name, occupation, salary
FROM employee_info
ORDER BY 1, 2;

Executing the statement will display the below result set:

In the statement, the name column is represented by 1, and the occupation column is represented by 2. This statement was executed successfully, but DBA considered the bad programming practice to use the ordinal positions of the columns in the ORDER BY clause. Some of the popular reasons are given below:

A table's columns do not have ordinal positions and must be referred to by their names.
When we edit the select list, it's possible to forget to update the corresponding changes in the ORDER BY clause.

Therefore, it is recommended to always specify the column names directly in the ORDER BY clause to remove future difficulties.

OFFSET and FETCH options in the ORDER BY Clause

OFFSET option is used in the ORDER BY clause to eliminate the number of records from the starting record (TOP). It means that OFFSET specifies the beginning point for returning rows from a result set.

The FETCH option in the ORDER BY clause is used to specify the number of rows return after the OFFSET clause is processed in a query.

Example

Suppose we want to get the employee records starting from 3 up to the next four rows. It can be retrieved easily by using the OFFSET and FETCH clauses. Here is the query:

SELECT name, occupation, salary
FROM employee_info
ORDER BY name   
OFFSET 3 ROWS   
FETCH NEXT 4 ROWS ONLY;  

Executing the statement will return the following output where OFFSET 3 skips the three rows and FETCH 4 ROWS ONLY displays the required result.

This article will explain in deep about the ORDER BY clause in the SQL Server. Here we learned what ORDER BY clause is, how we use it in different cases, and how the OFFSET and FETCH option works with this clause in detail.

Next TopicSQL Server HAVING

← prev next →