ORDER BY Clause in SQL

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

The ORDER BY is an optional clause of the SELECT statement. The ORDER BY clause allows you to sort the rows returned by the SELECT clause by one or more sort expressions in ascending or descending order.

The following shows the syntax of the ORDER BY clause:

SELECT 
    select_list
FROM
    table_name
ORDER BY 
    sort_expression [ASC | DESC];

In this syntax:

• First, place the ORDER BY clause after the FROM clause. The database will evaluate the SELECT statement with the ORDER BY * clause in the following order: FROM > SELECT > ORDER BY.
• Second, specify a sort expression after the ORDER BY clause. The sort expression specifies the sort criteria.
• Third, use ASC option to sort the result set by the sort expression in ascending order and DESC to sort the result set by the sort expression in the descending order.

Note that the ORDER BY clause uses the ASC option by default if you don’t either ASC or DESC.

The ORDER BY clause also allows you to sort the result set by multiple expressions. In this case, you need to use a comma to separate two sort expressions:

SELECT 
    select_list
FROM
    table_name
ORDER BY 
    sort_expression_1 [ASC | DESC],
    sort_expression_2 [ASC | DESC];

In this syntax, the ORDER BY clause sorts the result set by the sort_expression_1 first, and then sorts the sorted result set by the sort_expression_2.

Note that if you don’t specify the ORDER BY clause, the SELECT statement will not sort the result set. It means that the rows in the result set don’t have a specific order.

SQL ORDER BY clause examples

We’ll use the employees table in the sample database for the demonstration.

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

The following SELECT statement returns the data from the employee id, first name, last name, hire date, and salary column of the employees table:

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

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |
|         102 | Lex         | De Haan     | 1993-01-13 | 17000.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         105 | David       | Austin      | 1997-06-25 |  4800.00 |
|         106 | Valli       | Pataballa   | 1998-02-05 |  4800.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
|         108 | Nancy       | Greenberg   | 1994-08-17 | 12000.00 |
|         109 | Daniel      | Faviet      | 1994-08-16 |  9000.00 |
|         110 | John        | Chen        | 1997-09-28 |  8200.00 |
...

As you can see clearly from the output, the rows do not have any order.

The following example uses the ORDER BY clause to sort employees by first names in alphabetical order:

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

The ORDER BY sorts the rows by the values in the first_name column.

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

The following example uses the ORDER BY clause to sort the employees by the first name in ascending order and the last name in descending order:

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

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         121 | Adam        | Fripp       | 1997-04-10 |  8200.00 |
|         115 | Alexander   | Khoo        | 1995-05-18 |  3100.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         193 | Britney     | Everett     | 1997-03-03 |  3900.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         179 | Charles     | Johnson     | 2000-01-04 |  6200.00 |
|         109 | Daniel      | Faviet      | 1994-08-16 |  9000.00 |
|         105 | David       | Austin      | 1997-06-25 |  4800.00 |
|         114 | Den         | Raphaely    | 1994-12-07 | 11000.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
|         118 | Guy         | Himuro      | 1998-11-15 |  2600.00 |
...

In this example, the ORDER BY clause 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 in the result set.

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

The following example uses the ORDER BY clause to sort employees by salary from high to low:

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

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |
|         102 | Lex         | De Haan     | 1993-01-13 | 17000.00 |
|         145 | John        | Russell     | 1996-10-01 | 14000.00 |
|         146 | Karen       | Partners    | 1997-01-05 | 13500.00 |
|         201 | Michael     | Hartstein   | 1996-02-17 | 13000.00 |
|         205 | Shelley     | Higgins     | 1994-06-07 | 12000.00 |
|         108 | Nancy       | Greenberg   | 1994-08-17 | 12000.00 |
|         114 | Den         | Raphaely    | 1994-12-07 | 11000.00 |
...

4) Using SQL ORDER BY to sort by dates example

Besides the character and numeric data, you can use the ORDER BY clause to sort rows by date. For example, the following statement uses the ORDER BY clause to sort the employees by values in the hire_date column from:

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

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         200 | Jennifer    | Whalen      | 1987-09-17 |  4400.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         102 | Lex         | De Haan     | 1993-01-13 | 17000.00 |
|         203 | Susan       | Mavris      | 1994-06-07 |  6500.00 |
|         204 | Hermann     | Baer        | 1994-06-07 | 10000.00 |
...

To see the employees who joined the company from lastest to earliest, you sort the employees by the hire dates in descending order:

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

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         179 | Charles     | Johnson     | 2000-01-04 |  6200.00 |
|         113 | Luis        | Popp        | 1999-12-07 |  6900.00 |
|         119 | Karen       | Colmenares  | 1999-08-10 |  2500.00 |
|         178 | Kimberely   | Grant       | 1999-05-24 |  7000.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
|         118 | Guy         | Himuro      | 1998-11-15 |  2600.00 |
|         126 | Irene       | Mikkilineni | 1998-09-28 |  2700.00 |
|         177 | Jack        | Livingston  | 1998-04-23 |  8400.00 |
|         176 | Jonathon    | Taylor      | 1998-03-24 |  8600.00 |
...