SQL Order By

Ordering the Result Set

Typically, when you use the SELECT statement to retrieve data from a table, the rows in the result set are not sorted in any specific order. If you need your result set to be ordered in a particular way, you can use the ORDER BY clause at the end of your query. This clause specifies how the server should sort the data returned by the query. By default, the sorting order is ascending.

Syntax

The ORDER BY clause is used to sort the data returned by a query in ascending or descending order. The basic syntax of this clause is:

Here, column_list represents the names of columns or fields like name, age, country, etc., in a database table from which you want to retrieve values. column_name is the name of the column you want to sort by. Let's explore some examples that demonstrate how this works.

Suppose we have an employees table in our database with the following records:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

Sorting Single Column

The following SQL statement retrieves all employees from the employees table and sorts the result set by the emp_name column in ascending order:

SELECT * FROM employees 
ORDER BY emp_name ASC;

You can omit the ASC option and use the following syntax. It will produce the same result set as the previous statement, as the default sorting order is ascending:

SELECT * FROM employees 
ORDER BY emp_name;

After running the above command, you will get output similar to this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

Similarly, you can use the DESC option to sort in descending order. The following statement orders the result set by the numeric salary column in descending order:

SELECT * FROM employees 
ORDER BY salary DESC;

This time, you will receive a result set similar to this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
+--------+--------------+------------+--------+---------+

Sorting Multiple Columns

You can also sort by multiple columns. However, the effect of this will only be noticeable if there are duplicate values in your table. Let's explore:

To better understand multi-column sorting, let's consider a table named trainees in our database with the following records:

+----+------------+------------+-------------+--------+
| id | first_name | last_name  | birth_date  | gender |
+----+------------+------------+-------------+--------+
|  1 | Peter      | Parker     | 1998-03-04  |  M     |
|  2 | Harry      | Potter     | 2001-08-30  |  M     |
|  3 | Peter      | Pan        | 2004-09-19  |  M     |
|  4 | Alice      | Kingsleigh | 1999-07-02  |  F     |
|  5 | John       | Connor     | 2002-01-15  |  M     |
+----+------------+------------+-------------+--------+

If you examine the table closely, you'll notice that there are some duplicate values. For instance, the full names of the trainees "Peter Parker" and "Peter Pan" are different, but their first names are the same.

Now execute the following command to sort the result set by the first_name column:

SELECT * FROM trainees 
ORDER BY first_name;

After execution, you will receive output similar to this:

+----+------------+------------+-------------+--------+
| id | first_name | last_name  | birth_date  | gender |
+----+------------+------------+-------------+--------+
|  4 | Alice      | Kingsleigh | 1999-07-02  |  F     |
|  2 | Harry      | Potter     | 2001-08-30  |  M     |
|  5 | John       | Connor     | 2002-01-15  |  M     |
|  1 | Peter      | Parker     | 1998-03-04  |  M     |
|  3 | Peter      | Pan        | 2004-09-19  |  M     |
+----+------------+------------+-------------+--------+

Now execute this statement which sorts the result set by first_name and last_name columns:

SELECT * FROM trainees 
ORDER BY first_name, last_name;

+----+------------+------------+-------------+--------+
| id | first_name | last_name  | birth_date  | gender |
+----+------------+------------+-------------+--------+
|  4 | Alice      | Kingsleigh | 1999-07-02  |  F     |
|  2 | Harry      | Potter     | 2001-08-30  |  M     |
|  5 | John       | Connor     | 2002-01-15  |  M     |
|  3 | Peter      | Pan        | 2004-09-19  |  M     |
|  1 | Peter      | Parker     | 1998-03-04  |  M     |
+----+------------+------------+-------------+--------+

Did you notice the difference between the previous and the current result set? This time, the record of the trainee "Peter Parker" comes after "Peter Pan".

Because both trainees have the same first name, "Peter", the second level of ordering is done by the last_name column. This is why the record of "Peter Parker" appears after "Peter Pan".