Oracle ORDER BY Clause

Summary: in this tutorial, you will learn how to use the Oracle ORDER BY clause to sort the result set by one or more columns in ascending or descending order.

Introduction to Oracle ORDER BY clause

In Oracle, a table stores its rows in unspecified order regardless of the order which rows were inserted into the database. To query rows in either ascending or descending order by a column, you must explicitly instruct Oracle Database that you want to do so.

For example, you may want to list all customers the by their names alphabetically or display all customers in order of lowest to highest credit limits.

To sort data, you add the ORDER BY clause to the SELECT statement 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], ...
Code language: CSS (css)

To sort the result set by a column, you list that column after the ORDER BY clause.

Following the column name is a sort order that can be:

  • ASC for sorting in ascending order
  • DESC for sorting in descending order

By default, the ORDER BY clause sorts rows in ascending order whether you specify ASC or not. If you want to sort rows in descending order, you use DESC explicitly.

NULLS FIRST places NULL values before non-NULL values and NULLS LAST puts the NULL values after non-NULL values.

The ORDER BY clause allows you to sort data by multiple columns where each column may have different sort orders.

Note that the ORDER BY clause is always the last clause in a SELECT statement.

Oracle ORDER BY clause examples

We will use the customers table in the sample database for demonstration.

customers table

The following statement retrieves customer name, address, and credit limit from the customers table:

SELECT name, address, credit_limit FROM customers;
Oracle ORDER BY - customer data

As you can see, the order of rows is unspecified.

A) Sorting rows by a column example

To sort the customer data by names alphabetically in ascending order, you use the following statement:

SELECT name, address, credit_limit FROM customers ORDER BY name ASC;
Oracle ORDER BY - sort by one column

The ASC instructs Oracle to sort the rows in ascending order. Because the ASC is optional. If you omit it, by default, the ORDER BY clause sorts rows by the specified column in ascending order.

Therefore, the following expression:

ORDER BY name ASC

is equivalent to the following:

ORDER BY name

To sort customer by name alphabetically in descending order, you explicitly use DESC after the column name in the ORDER BY clause as follows:

SELECT name, address, credit_limit FROM customers ORDER BY name DESC;

The following picture shows the result that customers sorted by names alphabetically in descending order:

Oracle ORDER BY - sort column descending order

B) Sorting rows by multiple columns example

To sort multiple columns, you separate each column in the ORDER BY clause by a comma.

See the following contacts table in the sample database.

contacts table

For example, to sort contacts by their first names in ascending order and their last names in descending order, you use the following statement:

SELECT first_name, last_name FROM contacts ORDER BY first_name, last_name DESC;
Code language: SQL (Structured Query Language) (sql)

In this example, Oracle first sorts the rows by first names in ascending order to make an initial result set. Oracle then sorts the initial result set by the last name in descending order.

See the following result:

Oracle ORDER BY - sort multiple columns

In this result:

  • First, the first names are sorted in ascending order.
  • Second, if two first names are the same, the last names are sorted in descending order e..g, Daniel Glass and Daniel Costner, Dianne Sen and Dianne Derek, Doretha Tyler and  Dorotha Wong.

C) Sort rows by column’s positions example

You don’t need to specify the column names for sorting the data. If you prefer, you can use the positions of the column in the ORDER BY clause.

See the following statement:

SELECT name, credit_limit FROM customers ORDER BY 2 DESC, 1;

In this example, the position of name column is 1 and credit_limitcolumn is 2.

In the ORDER BY clause, we used these column positions to instruct the Oracle to sort the rows.

C) Sorting rows with NULL values examples

See the following locations table in the sample database:

locations table

The following statement retrieves locations and sorts them by city and state:

SELECT country_id, city, state FROM locations ORDER BY city, state;

Here is the result:

Oracle ORDER BY - NULLS first

The state column has NULL values, meaning that the state data is not relevant to some cities e.g., Beijing, Hiroshima, and London.

When sorting mixed NULL with non-NULL values, Oracle allows you to specify which one should appear first.

For example, the following statement sorts the locations by state in ascending order and places NULL values first.

SELECT country_id, city, state FROM locations ORDER BY state ASC NULLS FIRST;
oracle order by - sort by nulls first example

To place NULL values after the non-NULL values, you use NULLS LAST as shown in the following statement:

SELECT country_id, city, state FROM locations ORDER BY state ASC NULLS LAST;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Oracle ORDER BY - NULLS Last

D) Sorting rows by the result of a function or expression

The ORDER BY clause allows you to apply a function e.g., string function and math function on a column and sorts the data by the result of the function.

For example, the following statement uses the UPPER() function in the ORDER BY clause to sort the customer names case-insensitively:

SELECT customer_id, name FROM customers ORDER BY UPPER( name );
Code language: SQL (Structured Query Language) (sql)

The following illustrates the result:

Oracle ORDER BY - sort by function

E) Sorting by date example

See the following orders table from the sample database:

orders table

This example uses the ORDER BY clause to sort orders by order date:

SELECT order_id, customer_id, status, order_date FROM orders ORDER BY order_date DESC;
Code language: SQL (Structured Query Language) (sql)
oracle order by - sort by date example

In this tutorial, you have learned how to use the Oracle ORDER BY clause to sort rows by one or more columns in ascending or descending order.

Was this tutorial helpful?