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: CSS (css)

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
...

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.

Databases #

Was this tutorial helpful ?