ORDER BY CLAUSE

The ORDER BY clause allows users to sort a result set prior to it being returned. It is perhaps the most straightforward clause among the BIG SIX because of users’ experiences using the popular spreadsheet software, Microsoft Excel. Like the sorting functionality afforded to users of Microsoft Excel, the ORDER BY clause permits users to sort result sets in ascending or descending order based on the values of one or more user-specified attributes. To illustrate the use of the ORDER BY clause, I will walk through a few examples. Following a few examples highlighting the clause’s basic usage, I’ll showcase a couple of additional examples aiming to explore the possible things a result set can be sorted by. Additionally, I will introduce a method to implement custom sorting and highlight how missing values are handled.

Before reading further, consider reviewing the Example Data section below to familiarize yourself with the data being used throughout the examples on this page. If you are already familiar with the data, then skip to the Basic Usage section.

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Basic Usage
Sorting a result set is as easy as specifying the attribute you wish to sort by in the ORDER BY clause followed by “ASC” or “DESC.” These two keywords are short for ascending and descending, respectively. If the sorting method is omitted from the ORDER BY clause, then ascending order (ASC) is implied. As a word of caution, keep in mind making use of defaults in any programming language means you are operating under the assumption that the reader also knows the defaults. That said, you will notice I do make use of the default sort method in the examples on this page by not explicitly stating “ASC.”

Carrying on, let us see how easy it really is. The code below returns one row per job ID value and the average salary of the corresponding employees holding the specific job. The result set is sorted alphabetically in ascending (i.e., A-Z) order by the job ID values by using the ORDER BY clause followed by the pre-existing attribute, job_id.

SELECT
	hr.employees.job_id,
	AVG(hr.employees.salary) AS average_salary
FROM
	hr.employees
GROUP BY
	hr.employees.job_id
ORDER BY
	hr.employees.job_id;

With an additional four characters, the result set can be sorted in descending order. See the code and corresponding output below.

SELECT
	hr.employees.job_id,
	AVG(hr.employees.salary) AS average_salary
FROM
	hr.employees
GROUP BY
	hr.employees.job_id
ORDER BY
	hr.employees.job_id DESC;

In the previous examples, the result sets were sorted based on a single pre-existing attribute’s values. To sort a result set by multiple attributes, use the ORDER BY clause followed by a comma-separated list of attributes you wish to sort by. A concrete example can be found below. The code returns one row per manager ID and job ID combination along with the corresponding average employee salary. The result set is sorted by the manager ID values, ascending (i.e., least to greatest). Each group of grouped rows is subsequently sorted by the job ID values in descending order. For clarity, see the output following the code below.

SELECT
    hr.employees.manager_id,
	hr.employees.job_id,
	AVG(hr.employees.salary) AS average_salary
FROM
	hr.employees
GROUP BY
    hr.employees.manager_id,
	hr.employees.job_id
ORDER BY
    hr.employees.manager_id,
	hr.employees.job_id DESC;

In the output above, notice all grouped rows corresponding to the manager ID value of 100 are next to each other and the corresponding job ID values are in descending order (i.e., Z-A). This is no surprise given the code used to generate the output, but is useful to connect the dots on how multi-attribute sorting works using SQL.

Having walked through the ORDER BY clause’s basic usage through a handful of examples, let us now turn to sorting result sets by things other than pre-existing attributes.

ORDER BY Non-selected Attributes
The ORDER BY clause permits users to sort a result set based on values that can be derived using the result set but are not specified in the SELECT clause. For example, suppose you are tasked with generating a sales representative performance report. For each sales representative, the total number of orders placed by customers assisted by the corresponding sales representative and the total order total value are required. Additionally, the report needs to be sorted in descending order based on each sales representative’s average order total. The code below achieves this task by sorting based on the results of computing the average order total across each set of grouped rows (i.e., each sales representative’s orders).

SELECT
    oe.orders.sales_rep_id,
    COUNT(oe.orders.order_id) AS order_count,
    SUM(oe.orders.order_total) AS total_order_total
FROM
    oe.orders
WHERE
    oe.orders.sales_rep_id IS NOT NULL
GROUP BY
    oe.orders.sales_rep_id
ORDER BY
	AVG(oe.orders.order_total) DESC;

ORDER BY Alias
Being able to sort a result set by an attribute’s alias can engender more readable code and save you keystrokes at the same time. In the example code below, the result set has been sorted in descending order based on the derived attribute, order_count, instead of the code used to produce the attribute and its values (i.e., COUNT(oe.orders.order_id)). While the benefit of this feature may not be significant here, the benefit is evident for derived attributes with more lengthy code required to produce them.

SELECT
    oe.orders.sales_rep_id,
    COUNT(oe.orders.order_id) AS order_count,
    SUM(oe.orders.order_total) AS total_order_total
FROM
    oe.orders
WHERE
    oe.orders.sales_rep_id IS NOT NULL
GROUP BY
    oe.orders.sales_rep_id
ORDER BY
	order_count DESC;

Custom Sorting
There may be instances where it is required or desirable to sort values in ways that go beyond the standard methods of ascending order and descending order. For example, suppose you are designing a sign-up form for your new technology newsletter. To subscribe to the newsletter, customers must fill out the form containing various fields, including a field called “Country Name.” This field is meant to indicate where the customer resides. You believe the vast majority of your subscribers will be residents of the United States, so to make it easier for the majority to fill out the form, you wish to have the value, “United States of America,” appear as the first value in the field’s drop-down selector and all subsequent country name values to appear in alphabetical order. How can this be achieved using SQL? One method is to use a CASE expression to implement conditional logic. In the code below, a CASE expression is used to return 0 when the country ID is “US” and 1 for all other country ID values. Sorting the numeric values returned results in the value, United States of America, appearing in the first row. The second argument or attribute specified in the ORDER BY clause is country name. This is required to ensure all country name values following the first value (i.e., United Statues of America) are sorted in alphabetical order. If this second attribute was not included in the ORDER BY clause, then the remainder of country names would be in no particular order.

SELECT
	hr.countries.country_name AS "Country Name"
FROM
    hr.countries
ORDER BY
    CASE
    	WHEN hr.countries.country_id = 'US'
			THEN 0
		ELSE 1
	END,
	hr.countries.country_name;

Handling Missing Values
Depending on the relational database management system (RDBMS) being used, NULL may appear at the beginning or at the end of a result set. For the examples on this page, Oracle Database 19c is used. By default, queries written using Oracle database will return NULLs first when the sorting specified is ascending and last when descending order is specified. It may be easier to think of it this way: For columns of numeric data type, NULL is treated as the largest value and for columns of character data type, NULL is last in terms of alphabetical order. An example for each scenario follows. For clarity, the code and corresponding output is provided.

-- Number of employees corresponding to each manager ID value.
SELECT
	hr.employees.manager_id,
    COUNT(hr.employees.employee_id) AS employee_count
FROM
	hr.employees
GROUP BY
	hr.employees.manager_id
ORDER BY
	hr.employees.manager_id;

Notice in the example above, the row corresponding to the single employee not reporting to a manager appears last. In the example below, the number of employees not belonging to a department appears first because the sorting specified is descending order.

-- Number of employees corresponding to each department name value.
SELECT
	hr.departments.department_name,
	COUNT(hr.employees.employee_id) AS employee_count
FROM
	hr.employees
    LEFT JOIN
    hr.departments
    	ON hr.employees.department_id = hr.departments.department_id
GROUP BY
	hr.departments.department_name
ORDER BY
	hr.departments.department_name DESC;

Knowledge Check
If you’d like to confirm your understanding on the ORDER BY clause and other fundamental concepts, consider solving a handful of my practice problems here.