In this lesson, you’ll learn how to sort a result set by one or more columns in ascending or descending order using the Oracle ORDER BY clause.
A table in Oracle stores its rows in an indeterminate order, regardless of how they were placed into the database. You must explicitly tell Oracle Database that you wish to query rows in either ascending or descending order by a column.
For example, you might wish to display all clients in order of lowest to maximum credit limits or alphabetically by their names.
The ORDER BY clause is added to the SELECT statement to order data as follows:
SELECT column_1, column_2, column_3, ... FROM table_name ORDER BY column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST], column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST], ...
You list a column after the ORDER BY clause to sort the result set by that column.
The column name is followed by a sort order, which can be:
Sorting in ascending order (ASC)
DESC is used to sort in descending order.
Whether you use ASC or not, the ORDER BY clause organises rows in ascending order by default. You must use DESC explicitly to sort rows in descending order.
NULL values are placed before non-NULL values in NULLS FIRST, and NULL values are placed after non-NULL values in NULLS LAST.
The ORDER BY clause allows you to sort data by multiple columns with various sort orders for each column.
In a SELECT statement, the ORDER BY clause is always the last clause.
ORDER BY clause examples in Oracle
From the customers table, the following statement obtains the customer’s name, address, and credit limit:
SELECT name, address, credit_limit FROM customers;
Example of sorting rows by a column
You can use the following statement to sort the customer data alphabetically in ascending order:
SELECT name, address, credit_limit FROM customers ORDER BY name ASC;
Oracle is told to sort the data in ascending order by the ASC. Because the ASC is an optional component. If you don’t specify it, the ORDER BY clause organises rows in ascending order by the chosen column by default.
As a result, we have the following expression:
ORDER BY name ASC
is same as the following
ORDER BY name
You use DESC after the column name in the ORDER BY clause to sort customers by name alphabetically in descending order, as seen below:
SELECT name, address, credit_limit FROM customers ORDER BY name DESC;
Example of sorting rows by several columns
To sort several columns, add a comma between each column in the ORDER BY clause.
For example, you could use the following statement to sort contacts by their initial names in ascending order and their last names in descending order:
SELECT first_name, last_name FROM contacts ORDER BY first_name, last_name DESC;
In this case, Oracle creates an initial result set by sorting the rows by first names in ascending order. Oracle then sorts the first result set in decreasing order by last name.
Sort rows by column position or index
For sorting the data, you don’t need to supply the column names. You can use the column positions in the ORDER BY clause if you like.
SELECT name, credit_limit FROM customers ORDER BY 2 DESC, 1;
The name column is in place 1 in this case, while credit limit column is in position 2.
These column positions were utilised in the ORDER BY clause to tell Oracle to sort the data.
Examples of sorting rows with NULL values
The following statement retrieves and sorts locations by city and state:
SELECT country_id, city, state FROM locations ORDER BY city, state;
The state field has NULL values, indicating that some cities, such as Beijing, Hiroshima, and London, do not have state data.
Oracle allows you to choose which of the mixed NULL and non-NULL values should display first when sorting.
The following line, for example, arranges the locations by state in ascending order, with NULL values appearing first.
SELECT country_id, city, state FROM locations ORDER BY state ASC NULLS FIRST;
NULLS LAST is used to place NULL values after non-NULL values, as shown in the following statement:
SELECT country_id, city, state FROM locations ORDER BY state ASC NULLS LAST;
Sorting rows based on a function or expression’s output
The ORDER BY clause allows you to apply a function to a column, such as a string function or a math function, then sort the data by the function’s result.
For example, to sort the customer names case-insensitively, the following line employs the UPPER() function in the ORDER BY clause:
SELECT customer_id, name FROM customers ORDER BY UPPER( name );
Example of date sorting
The ORDER BY clause is used in this example to arrange orders by order date: