SQL ORDER BY

Summary: This tutorial shows you how to use the SQL ORDER BY clause to sort the result set based on specified criteria in ascending or descending orders.

Introduction to SQL ORDER BY clause

When you use the SELECT statement to query data from a table, the order in which rows appear in the result set may not be what you expected.

In some cases, the rows that appear in the result set are in the order that they are stored in the table physically. However, in case the query optimizer uses an index to process the query, the rows will appear as they are stored in the index key order. For this reason, the order of rows in the result set is undetermined or unpredictable.

The query optimizer is a built-in software component in the database system that determines the most efficient way for an SQL statement to query the requested data.

To specify exactly the order of rows in the result set, you add to use an ORDER BY clause in the SELECT a statement as follows:

SELECT 
    column1, column2
FROM
    table_name
ORDER BY column1 ASC , 
         column2 DESC;

In this syntax, the ORDER BY the clause appears after the FROM clause. In case the SELECT statement contains a WHERE clause, the ORDER BY the clause must appear after the WHERE clause.

To sort the result set, you specify the column in which you want to sort and the kind of the sort order:

• Ascending ( ASC)
• Descending ( DESC)

If you don’t specify the sort order, the database system typically sorts the result set in ascending order ( ASC) by default.

When you include more than one column in the ORDER BY clause, the database system first sorts the result set based on the first column and then sorts the sorted result set based on the second column, and so on.

SQL ORDER BY clause examples

We will use the employees the table in the sample database for the demonstration.

1) Using SQL ORDER BY clause to sort values in one column example

The following statement retrieves the employee id, first name, last name, hire date, and salary from the employees table:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees;

It seems that the rows appear as they are stored in the  employees table. To sort employees by first names alphabetically, you add an ORDER BY clause to query as follows:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	first_name;

The result set now is sorted by the first_name column.

2) Using SQL ORDER BY  clause to sort values in multiple columns example

To sort the employees by the first name in ascending order and the last name in descending order, you use the following statement:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	first_name,
	last_name DESC;

First, the database system sorts the result set by the first name in ascending order, then it sorts the sorted result set by the last name in descending order. Notice the change in position of two employees: Alexander Khoo and Alexander Hunold

3) Using SQL ORDER BY clause to sort values in a numeric column example

SQL allows you to sort data alphabetically as shown in the previous example and also sort data numerically. For example, the following statement selects employee data and sorts the result set by salary in descending order:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	salary DESC;

4) Using SQL ORDER BY to sort dates example

Besides the character and numeric, SQL allows you to sort the result set by date. The following statement sorts the employees by values in the hire_date column in ascending order.

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	hire_date;

To view the latest employees who have just joined the company, you sort the employees by the hire dates in descending order as shown in the following statement:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	hire_date DESC;

In this tutorial, you have learned how to use the SQL ORDER BY clause to sort the result set based on one or more columns in ascending or descending order.