Sorting Retrieved Data—ORDER BY

Sorting retrieved is one important aspect while presenting data. Just imagine if we need employee data to be organised in ascending or descending order. Or we need the employee records to be organised based on the amount of salary earned or even according to hire date. That is when the ORDER BY keyword becomes handy.

In SQL, we can order retrieved data in ascending or descending order based on any column we wish. To use the ORDER BY keyword, we bring it last in any SELECT statement. It should be preceded by the column name that we want to use as a basis for organisation and finally the order of organisation (ascending or descending order).

Syntax

SELECT column_name FROM table_name ORDER BY column_name order;

Example: Let us order records in the employees table using the first name as the basis of organising in ascending order.

SELECT first_name, last_name, salary FROM employees ORDER BY first_name ASC;

As you may have witnessed, data is organised in ascending order basing on the first name of the employee. However, we may need the data to be in descending order. For this, we just change the order.

SELECT first_name, last_name, salary FROM employees ORDER BY first_name DESC;

If we don’t specify the order of sorting, results are by default returned in ascending order.

SELECT first_name, last_name, salary FROM employees ORDER BY first_name;

Using column alias for sorting

We can decide to use column alias in the place of column names in the ORDER BY clause.

SELECT first_name, last_name AS surname FROM employees ORDER BY surname ASC;

In case the column alias was in double quotes, it should also be in double quotes as it appears in the SELECT clause.

SELECT first_name, last_name, salary AS "Employee Salary" FROM employees ORDER BY "Employee Salary" DESC;

Using column position for sorting

We can as well use column positions to sort data rather than column names and alias. This is how it is done. In our SELECT statement, count the number of columns we have. Count 1 for the first column, 2 for the second column, and so on. Note the position of the column we want to use for sorting and use its position.

For example, We need to use last_name as the column to be used for sorting. In the SELECT statement, it is in the 2nd position. Therefore, in the ORDER BY clause, instead of writing last_name; we write 2 since it is in the second position.

SELECT first_name, last_name, salary AS "Employee Salary" FROM employees ORDER BY 2 DESC;

Sorting for nulls

Nulls means nothing. There are some cases when a record is missing some values. For example, we could have an employee record that doesn’t have an email address or contact. These are what we call nulls. We may find instances where we want to sort such values for easy identification rather than getting them mixed in a sea of records. To do this, we use NULLS FIRST or NULLS last in the ORDER BY clause after the sorting order.

For example, we want employee records to be organised basing on the commission an employee gets. However, those employees without a commission should be at the top.

SELECT last_name, commission_pct FROM employees ORDER BY 2 DESC NULLS FIRST;

Null values can also be put at the bottom. Here, we use the NULLS LAST keyword.

SELECT last_name, commission_pct FROM employees ORDER BY 2 DESC NULLS LAST;

Summary

1. ORDER BY keyword is used for sorting retrieved data.

2. It MUST always be the last in SELECT statement.

3. It is preceded with a name of the column we want to use as the basis for sorting.

4. The column name is preceded with the sorting order (ascending or descending). If no order specified, results are sorted in ascending order.

5. We can use column alias or column positions for sorting data rather than column names.

6. We use the NULLS FIRST or NULLS LAST to sort null values.