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 #

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

For example, you may want to list all customers alphabetically by name or display them in order from lowest to highest credit limit.

To sort the rows returned by the SELECT statement, 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],
    column_2 [ASC | DESC],
    ... 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 the DESC explicitly.

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

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

When executing a statement that has SELECT, FROM, and ORDER BY clauses, Oracle evaluates clauses in the following order:

1. FROM
2. ORDER BY
3. SELECT

Sorting rows by a column example #

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

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)

Try it

Output:

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)

Try it

The following picture shows the result that ORDER BY clause sorts the customers by name alphabetically in descending order:

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)

Try it

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.

Sort rows by column positions #

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. For example:

SELECT
  name,
  credit_limit
FROM
  customers
ORDER BY
  2 DESC,
  1;Code language: SQL (Structured Query Language) (sql)

Try it

In this example, the position of name column is 1 and credit_limitcolumn is 2. In the ORDER BY clause, we use these column positions to instruct the Oracle to sort the rows.

Sorting NULL #

In Oracle, NULL is a placeholder that indicates the data is missing, unknown, or not applicable.

For example, some countries have states, whereas other countries do not. To represent the state of the countries that do not have, you can use NULL. In this case, NULL means not applicable.

Since NULL is unknown, it is not equal to any value, even itself. The reason is that two unknown values may not be equal.

When you use the ORDER BY clause to sort rows that have NULL, you have two options:

• NULLS FIRST places NULL before non-NULL values.
• NULLS LAST places NULL after non-NULL values.

SELECT column_list
FROM table_name
ORDER BY [ASC | DESC] [NULLS FIRST | NULLS LAST];Code language: SQL (Structured Query Language) (sql)

See the following locations table in the sample database:

The following statement uses the SELECT statement to retrieve data from the locations table and sorts locations by city and state:

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

Try it

Here is the result:

The output indicates that the state column has NULL such as Beijing, Hiroshima, and London.

The following statement sorts locations by state in ascending order and places NULL first:

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

Try it

To place NULL 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)

Try it

Output:

Sorting rows by the result of a function or expression #

The ORDER BY clause allows you to apply a function e.g., string functions or math functions, to 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)

Try it

Output:

Sorting by dates #

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)

Try it

Summary #

• Use the Oracle ORDER BY clause to sort rows by one or more columns.
• Use the ASC to sort rows in ascending order.
• Use the DESCto sort rows in descending order.
• Use the NULLS FIRST to place NULL before other non-null values.
• Use the NULLS LAST to place NULL after other non-null values.

Quiz #