SQL ORDER BY Clause
SQL ORDER BY clause can be used to sort the records returned by a SELECT statement, by adding the clause ORDER BY at the end of a statement.
- Without the ORDER BY clause, the records returned by a SELECT statement are not in any particular order.
- The ORDER BY clause must be defined at the end of a SELECT statement.
- The sort order can be ascending or descending.
- The default sort order is ascending.
- The column name must be stated before defining the sort order.
In the previous chapters, we have seen how to filter records using AND, OR, IN, and BETWEEN logical operators.
Now, let's see how to sort the records returned by a SELECT statement using the ORDER BY clause.
Syntax
Here is the basic syntax for the ORDER BY clause on a SELECT statement.
- The column_list is a list of column names separated by a comma.
- The table_name is the name of the table.
- The column_name is the name of the column based on which sort must happen.
- The sot order ASC for ascending and DESC for descending. If it is not specified, then the default sort order ASC is considered.
SELECT column_list FROM table_name
ORDER BY column_name ASC|DESC;Table Data
Let's consider the below data in the table employees before executing any of the below queries.
| employee_id | employee_name | gender | birth_date | hire_date | salary |
| 1 | Robin Hood | M | 1990-10-10 | 2010-01-15 | 25000 |
| 2 | Tony Blank | M | 1982-08-07 | 2010-01-15 | 89000 |
| 3 | Andrew Russel | M | 1998-05-04 | 2010-01-15 | 28000 |
| 4 | James Cooper | M | 2000-10-20 | 2015-05-10 | 45000 |
| 5 | Rose Cutler | F | 1985-08-08 | 2015-06-21 | 65000 |
Sorting Single Column
The below SQL statement returns all the employees, with the records sorted in ascending order by employee name.
SELECT * FROM employees
ORDER BY employee_name ASC;Or, we can even skip ASC as it is the default sort order, which will be applied automatically if not specified.
SELECT * FROM employees
ORDER BY employee_name;After successful execution, the output looks like this, where the records are sorted by employee name in ascending order.
| employee_id | employee_name | gender | birth_date | hire_date | salary |
| 3 | Andrew Russel | M | 1998-05-04 | 2010-01-15 | 28000 |
| 4 | James Cooper | M | 2000-10-20 | 2015-05-10 | 45000 |
| 1 | Robin Hood | M | 1990-10-10 | 2010-01-15 | 25000 |
| 5 | Rose Cutler | F | 1985-08-08 | 2015-06-21 | 65000 |
| 2 | Tony Blank | M | 1982-08-07 | 2010-01-15 | 89000 |
Similarly, the below SQL statement returns all the employees, with the records sorted in descending order by salary.
SELECT * FROM employees
ORDER BY salary DESC;After successful execution, the output looks like this, where the records are sorted by salary in ascending order.
| employee_id | employee_name | gender | birth_date | hire_date | salary |
| 2 | Tony Blank | M | 1982-08-07 | 2010-01-15 | 89000 |
| 5 | Rose Cutler | F | 1985-08-08 | 2015-06-21 | 65000 |
| 4 | James Cooper | M | 2000-10-20 | 2015-05-10 | 45000 |
| 3 | Andrew Russel | M | 1998-05-04 | 2010-01-15 | 28000 |
| 1 | Robin Hood | M | 1990-10-10 | 2010-01-15 | 25000 |
Sorting Multiple Columns
The ORDER BY clause can also be used to sort the records by multiple columns as explained below.
- After the ORDER BY clause, specify the columns and their sort orders, separating each of them with a comma.
- The multiple columns sort can only be visible or recognized when we have duplicates on at least one of the columns included in the sort.
If we look at the table data carefully, we can identify the first three records have the same hire_date, which means we have duplicates.
The below SQL statement returns the employees, with the records sorted in ascending order by both hire_date and employee_name.
SELECT * FROM employees
ORDER BY hire_date ASC, employee_name ASC;Or, we can skip ASC on one or both the columns, as it is the default sort order, which will be applied automatically if not specified.
SELECT * FROM employees
ORDER BY hire_date, employee_name;After successful execution, the output looks like this, where the records are sorted by both hire_date and employee_name in ascending order.
| employee_id | employee_name | gender | birth_date | hire_date | salary |
| 3 | Andrew Russel | M | 1998-05-04 | 2010-01-15 | 28000 |
| 1 | Robin Hood | M | 1990-10-10 | 2010-01-15 | 25000 |
| 2 | Tony Blank | M | 1982-08-07 | 2010-01-15 | 89000 |
| 4 | James Cooper | M | 2000-10-20 | 2015-05-10 | 45000 |
| 5 | Rose Cutler | F | 1985-08-08 | 2015-06-21 | 65000 |
Similarly, the below SQL statement returns all the employees, with the records sorted by the hire_date in ascending and the employee_name in descending.
SELECT * FROM employees
ORDER BY hire_date ASC, employee_name DESC;After successful execution, the output looks like this, where the records are sorted by the hire_date in ascending and the employee_name in descending.
| employee_id | employee_name | gender | birth_date | hire_date | salary |
| 2 | Tony Blank | M | 1982-08-07 | 2010-01-15 | 89000 |
| 1 | Robin Hood | M | 1990-10-10 | 2010-01-15 | 25000 |
| 3 | Andrew Russel | M | 1998-05-04 | 2010-01-15 | 28000 |
| 4 | James Cooper | M | 2000-10-20 | 2015-05-10 | 45000 |
| 5 | Rose Cutler | F | 1985-08-08 | 2015-06-21 | 65000 |
Other Statements
The ORDER BY clause can only be applied to a SELECT statement.
Overall
We now know how to sort records returned by a SELECT statement using the ORDER BY clause.