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: SQL (Structured Query Language) (sql)

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.

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

SELECT
    name,
    address,
    credit_limit
FROM
    customers;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 ASCCode language: SQL (Structured Query Language) (sql)

is equivalent to the following:

ORDER BY nameCode language: SQL (Structured Query Language) (sql)

To sort customers 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;Code language: SQL (Structured Query Language) (sql)

The following picture shows the result that customers sorted by names alphabetically in 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.

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:

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;Code language: SQL (Structured Query Language) (sql)

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:

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

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

Here is the result:

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;Code language: SQL (Structured Query Language) (sql)

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:

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 sort 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:

E) Sorting by date example

See the following orders table from the sample database:

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)

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.