SQL Order By

For the default behavior of a SQL query, there is no guarantee as to the ordering of the records which are returned. This may depend on the way the data are stored, the query engine, or the query itself. The SQL ORDER BY clause is used with a SELECT statement to sort the results, ensuring consistent ordering every time it is run. It takes the form as below:

SELECT <columns> FROM <table> ORDER BY <column>;

In a more complex query, if a WHERE clause is included, the ORDER BY clause follows it and comes last:

SELECT <columns> FROM <table> WHERE <predicate> ORDER BY <column>;

The default behavior of the ORDER BY clause is to sort the data in ascending order. As with the WHERE clause, the column used to sort the records returned need not explicitly be included in those specified in the SELECT statement though this is considered a best practice.

Let’s take a look at the employees table again. Suppose we wanted to return the employees who have been with the company the longest. We can achieve this result by sorting on the hire_date column in ascending order using ORDER BY:

SELECT * FROM employees ORDER BY hire_date;

We can see there are multiple employees who started on January 1st, 1985. If we wanted to sort the results in descending order, we would need to use the ORDER BY clause and include the DESC (short for descending) keyword, which follows the column name and is the last part of the query:

SELECT * FROM employees ORDER BY hire_date DESC;

Above we can see that the results have been sorted by hire_date in descending order, with the most recent dates first.

Though it is not required for the query to execute, as sorting in ascending order is the default behavior, there is also an ASC keyword. This should be included as a best practice such that queries are as explicit and comprehensible for the reader as possible. 

For example, the two queries below are functionally equivalent and return the same results, but the latter with explicit usage of ASC is preferred:

SELECT * FROM employees ORDER BY hire_date;

SELECT * FROM employees ORDER BY hire_date ASC;