Sorting Data with ORDER BY

In most of the cases, the retrieved data is displayed in the order that is saved on the tables in the database but it is not guaranteed that the result will always be the same due to changes such as updating and deleting.
This can be solved by adding the ORDER BY clause followed by the Column name and the sorting method, but it has to be added as the last clause in the SQL statement.

ASE - Ascending order
DESC - Descending order

The default ordering is ascending and the numeric values are given priority as lowest first, whereas date values has the earliest date first and Character values in the alphabetical order. In addition, it is not necessary to have the sorting column to be on the resulting list.

Syntax

SELECT expression
FROM Table
ORDER BY [Column_name] [ASE|DESC];

ORDER BY with default sorting

Now lets put that to practice by retriving the employees in the order they were hired.

SQL statement

SELECT last_name, job_id, department_id, hire_date
FROM Employees
ORDER BY hire_date;

This is a sort on a date field and the post-fix is not mentioned therefore it should call its default which is Ascending.

Result

Select * from Employees order by hire_date

Sorting in the descending order

Now let us try the decending order on the same query.

SQL statement

SELECT last_name, job_id, department_id, hire_date
FROM Employees
ORDER BY hire_date DESC;

Result

Select * from Employees Order by hire_date DESC

Sorting by column alias

It is also possible to use the alias when sorting the tables. Lets put that into practise.

SQL Statement

SELECT employee_id, last_name, salary as "Monthly salary"
FROM employees
ORDER BY "Monthly salary";

The salary column is renamed to “Monthly salary” with an alias and the alias is used to sort the result.

Output

Sorting columns by multiple columns

It is also possible to sort multiple columns. The following sample will demonstrate the sorting using the
ORDER BY keyword.

SQL Statement

SELECT employee_id, last_name, salary
FROM employees
ORDER BY department_id, salary DESC;

Result