Sorting a Result Set By Using SQL ORDER BY Clause

Summary: in this tutorial, you will learn how to use SQL ORDER BY clause to sort the result set based on different criteria.

Introduction to SQL ORDER BY clause

To sort a result set returned by a SELECT statement, you use the  ORDER BY clause. The following query illustrates how to use the  ORDER BY clause in a SELECT statement:

The  ORDER BY clause allows you to sort the result set by a column or an expression with a condition that the value in the column or the returned value of the expression must be sortable i.e., the data type of the result must be character, numeric or date time.

To sort a result set in ascending order, you use ASC keyword, and in descending order, you use the DESC keyword. If you don’t specify any keyword explicitly, the  ORDER BY clause sorts the result set in ascending order by default.

To sort multiple columns, you just need to specify additional columns in the  ORDER BY clause. You can sort by one column in ascending order and another column in descending order.

SQL ORDER BY examples

Let’s take look at some examples of sorting result sets using the  ORDER BY clause.

SQL ORDER BY one column example

For example, you can sort all employees by last name in ascending order as the following query:

SQL Order By Lastname

SQL ORDER BY multiple columns example

You can sort the result set by multiple columns. The following query sorts employees by last name in descending order and first name in ascending order:

SQL Order By Example 2

The database engine sorts the result set based on last name in descending order first. And then it sorts the sorted result set by first name in ascending order to produce the final result set.

SQL ORDER BY with expressions

The  ORDER BY clause can also accept expressions. For example, you can use the CONCAT string function to construct full names of employees, and then sort the result set by the full name as the following query:

SQL Order By Example 3

The column alias is used for formatting the output of the result set. You can use the column alias in the ORDER BY clause rather than expression. The following query produces the same output:

SQL ORDER BY with positional number

The positional number is the position of the column in the SELECT clause. The position number starts with 1, 2, 3, etc. SQL allows you to use these positional numbers rather than columns or expressions to sort the result set.

The following statement sorts the employees by hired date in descending order to find the most junior employees in the company:

SQL Order By Example 4

SQL sorts the result set by hiredate column, which has positional number 3.

The positional number that refers to a specific column is changed when you change the columns in the  SELECT clause. This may lead to an unexpected result if you forget to change the positional number. Therefore, it is not recommended to use positional number in  ORDER BY clause. You only use it if you have no choice.

In this tutorial, you’ve learned how to use the  ORDER BY clause to sort result sets returned by a SELECT statement.