SQL ORDER BY

Summary: In this tutorial, you’ll learn how to use the SQL ORDER BY clause to sort the result set based on values of one or more rows 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 result set by one or more sort expressions in ascending and/or descending order.

Here’s the syntax of the ORDER BY clause:

SELECT
  select_list
FROM
  table_name
ORDER BY
  sort_expression [ASC | DESC];Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify a sort expression (sort_expression) in the ORDER BY clause based on which you want to sort the result set. The sort_expression can be a table column or an expression that involves a table column.
• Second, use ASC to sort the result set in ascending order and DESC to sort the result set in descending order.

ASC and DESC stand for ascending and descending respectively.

The ORDER BY clause uses the ASC option by default. It means that the ORDER BY clause sorts the rows in the result set by the sort_expression in ascending order if you don’t specify either ASC or DESC.

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

The ORDER BY clause allows you to sort the rows in the result set by multiple expressions. In this case, you need to use a comma-separated list of sort expressions in the ORDER BY clause:

SELECT
  select_list
FROM
  table_name
ORDER BY
  sort_expression_1 [ASC | DESC],
  sort_expression_2 [ASC | DESC];Code language: SQL (Structured Query Language) (sql)

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.

The database system evaluates the SELECT statement with the ORDER BY clause in the following order:

• FROM
• SELECT
• ORDER BY

Since the database system evaluates the ORDER BY clause after the SELECT clause, you can use column aliases in the ORDER BY clause.

SQL ORDER BY clause examples #

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

Sorting rows by one column example #

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

SELECT
  employee_id,
  first_name,
  last_name
FROM
  employees
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 employee_id | first_name  |  last_name
-------------+-------------+-------------
         121 | Adam        | Fripp
         103 | Alexander   | Hunold
         115 | Alexander   | Khoo
         193 | Britney     | Everett
         104 | Bruce       | Ernst
...

In this example, the ORDER BY clause sorts the rows of the result set by the values in the first_name column.

Sorting rows by multiple columns #

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
FROM
  employees
ORDER BY
  first_name,
  last_name DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 employee_id | first_name  |  last_name
-------------+-------------+-------------
         121 | Adam        | Fripp
         115 | Alexander   | Khoo
         103 | Alexander   | Hunold
         193 | Britney     | Everett
         104 | Bruce       | Ernst
         179 | Charles     | Johnson
...

In this example, the ORDER BY clause sorts rows by the first name in ascending order, then sorts the sorted result set by the last name in descending order.

Notice the change in the position of two employees: Alexander Khoo and Alexander Hunold in the result set.

Sorting rows by 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,
  salary
FROM
  employees
ORDER BY
  salary DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 employee_id | first_name  |  last_name  |  salary
-------------+-------------+-------------+----------
         100 | Steven      | King        | 24000.00
         101 | Neena       | Kochhar     | 17000.00
         102 | Lex         | De Haan     | 17000.00
         145 | John        | Russell     | 14000.00
         146 | Karen       | Partners    | 13500.00
         201 | Michael     | Hartstein   | 13000.00

Sorting rows by dates example #

Besides the character and numeric data, you can use the ORDER BY clause to sort rows by dates.

For example, the following statement uses the ORDER BY clause to sort the employees by hire dates from earliest to latest:

SELECT
  first_name,
  last_name,
  hire_date
FROM
  employees
ORDER BY
  hire_date;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  |  last_name  | hire_date
-------------+-------------+------------
 Steven      | King        | 1987-06-17
 Jennifer    | Whalen      | 1987-09-17
 Neena       | Kochhar     | 1989-09-21
 Alexander   | Hunold      | 1990-01-03
 Bruce       | Ernst       | 1991-05-21
 Lex         | De Haan     | 1993-01-13

To sort the employees by the hire dates in descending order, you can use the following query:

SELECT
  first_name,
  last_name,
  hire_date
FROM
  employees
ORDER BY
  hire_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  |  last_name  | hire_date
-------------+-------------+------------
 Charles     | Johnson     | 2000-01-04
 Luis        | Popp        | 1999-12-07
 Karen       | Colmenares  | 1999-08-10
 Kimberely   | Grant       | 1999-05-24
 Diana       | Lorentz     | 1999-02-07
...

Sorting NULLs #

In SQL, NULL is a marker that indicates missing data or unknown value. NULL is special because you cannot compare it with any value.

If you want to sort rows by a column that has NULL, you can have an option to place NULLs before or after other regular values.

To place NULLs before other values, you use the NULLS FIRST option in the ORDER BY clause:

ORDER BY sort_expression NULLS FIRSTCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want to place NULL after other regular values, you use the NULLS LAST option:

ORDER BY sort_expression NULLS LASTCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the ORDER BY clause with the NULLS FIRST option to place the NULLs before other values:

SELECT
  first_name,
  phone_number
FROM
  employees
ORDER BY
  phone_number NULLS FIRST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name  | phone_number
-------------+--------------
 John        | NULL
 Charles     | NULL
 Kimberely   | NULL
 Jack        | NULL
 Jonathon    | NULL
 Karen       | NULL
 Jennifer    | 515.123.4444
 Steven      | 515.123.4567
...Code language: plaintext (plaintext)

The following query uses the ORDER BY clause with the NULLS LAST option to place the NULLs after other values:

SELECT
  first_name,
  phone_number
FROM
  employees
ORDER BY
  phone_number NULLS LAST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  | phone_number
-------------+--------------
 ...
 Irene       | 650.124.1224
 Sarah       | 650.501.1876
 Britney     | 650.501.2876
 Kimberely   | NULL
 Jack        | NULL
 Jonathon    | NULL
 Karen       | NULL
 John        | NULL
 Charles     | NULLCode language: plaintext (plaintext)

Summary #

• Use the ORDER BY clause to sort rows in a result set.
• Use the ASC option to sort rows in ascending order and DESC option to sort rows in descending order.
• Use NULLS FIRST to place NULLs before and NULLS LAST to place NULLs after other non-NULL values.

Databases #

• PostgreSQL ORDER BY clause
• Oracle ORDER BY clause
• SQL Server ORDER BY clause
• MySQL ORDER BY clause
• SQLite ORDER BY clause
• Db2 ORDER BY clause
• MariaDB ORDER BY clause

Quiz #