SQL ORDER BY Clause

The ORDER BY clause is used to sort the result set of a query according to the values in one or more specific columns.

The ORDER BY clause support sorting by multiple columns.

The default order used by ORDER BY is ascending. To sort the records in descending order, use the DESC keyword.

Syntax

The basic syntax of ORDER BY can be given with:

SELECT column_list
FROM table_name
ORDER BY column1_name, column2_name, ... ASC|DESC;

Parameters:

column_list: List of columns of the table

column1_name, column2_name: List of columns that will participate in sorting.

Demo Table

Consider that we have a table called "employee" with the following data:

Example Sorting Results in Ascending Order

To sort the result set in ascending order, you can specify the ASC key. If no value (ASC/DESC) is passed to the ORDER BY clause, the sort order will default to ascending order.

The following SQL statement will return all the employees for the table 'employee' and will order the result set by the 'first_name' column in ascending order.

SELECT * 
FROM employee 
ORDER BY first_name ASC;

You can skip the ASC attribute because the ORDER BY clause's default sorting order is ascending. So the following SQL statement will return the same result.

SELECT *
FROM employee
ORDER BY first_name;

After running one of the above SQL statement, you will get the following output:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Example Sorting Results in Descending Order

Similarly, you can pass the DESC attribute to the ORDER BY clause to perform sorting in descending order.

The following SQL statement will sort employees by the numeric salary column in descending order.

SELECT *
FROM employee
ORDER BY salary DESC;

After the execution, we will get the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Example Sorting Results by Relative Position

The ORDER BY clause can sort the result set using the relative position. For the relative position, the first field in the result set is 1, the second field is 2, and so on.

The following SQL statement will sort the result set by the relative position 1 ("emp_no" field) in descending order.

SELECT *
FROM employee
ORDER BY 1 DESC;

After the execution, the result set will be as the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
+--------+-------------+-----------+------------+------------+-----------+------------------+

It is equivalent to the following SQL statement:

SELECT *
FROM employee
ORDER BY emp_no DESC;

Example Sorting by Multiple Columns

The ORDER BY clause supports the sort in multiple columns. However, the change in the result will not be apparent until there are some duplicate values in the table.

Let us consider the same table "employee" that we used above:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

As you can see in this table, we have some duplicate values. The employee "James Smith" and "James Johnson" have the same first name but different last name.

Now let us execute the following SQL statement that will sort the result by the "first_name" column.

SELECT *
FROM employee
ORDER BY first_name;

After running the above SQL statement, the result set will look like the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Now, let us execute the following SQL statement that will sort by the "first_name" and the "last_name" columns.

SELECT *
FROM employee
ORDER BY first_name, last_name;

After the execution, we will get the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Did you see the difference between the previous and the current result? This time the record of the employee "James Johnson" comes before the "James Smith".

Since the value of the "first_name" column for both employees are the same "James", so the second level sorting is performed at the "last_name" column that explains why the employee "James Johnson" comes before "James Smith".

Note: When the ORDER BY clause use multiple columns for sorting, the result set is initially ordered by the first column, then that sorted list is ordered by the second column, and so on.

Example Sorting by Multiple Columns Using ASC and DESC

The ORDER BY clause supports the use of the ASC and DESC attributes in the same SELECT statement.

The following SQL statement will return all employees sorted first by "dept_name" in ascending order, and secondly sorted by "salary" in descending order.

SELECT *
FROM employee
ORDER BY dept_name ASC, salary DESC;

After the execution, we will get the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
+--------+-------------+-----------+------------+------------+-----------+------------------+