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

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];Code language: SQL (Structured Query Language) (sql)

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

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;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+------------+----------+
| 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;Code language: SQL (Structured Query Language) (sql)
Try It
+-------------+-------------+-------------+------------+----------+
| 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 |
...
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;Code language: SQL (Structured Query Language) (sql)
Try It
+-------------+-------------+-------------+------------+----------+
| 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;Code language: SQL (Structured Query Language) (sql)
Try It
+-------------+-------------+-------------+------------+----------+
| 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;Code language: SQL (Structured Query Language) (sql)
Try It
+-------------+-------------+-------------+------------+----------+
| 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;Code language: SQL (Structured Query Language) (sql)
Try It
+-------------+-------------+-------------+------------+----------+
| 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 |
...
Summary
Use the ORDER BY clause to sort rows returned by the SELECT clause.
Use the ASC option to sort rows in ascending order and DESC option to sort rows in descending order.

Share this post