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. SELECT
3. ORDER BY

Since Oracle evaluates the ORDER BY clause after the SELECT clause, you can sort values in a column by its column alias:

SELECT
  column1 alias
FROM
  table_name
ORDER BY
  alias;Code language: SQL (Structured Query Language) (sql)

Sorting rows by a column example #

We’ll use the customers table in the sample database for demonstration.

The following statement retrieves data from the customers table and sort them alphabetically by name:

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

Try it

Output:

The ASC tells Oracle to sort the rows in ascending order.

Since ASC keyword is default and optional; the following clauses are equivalent:

ORDER BY name ASCCode language: SQL (Structured Query Language) (sql)

and

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

To sort customers by name alphabetically in descending order, you explicitly use DESC after the name column 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

Output:

The output shows that the statement sorts the customers by name alphabetically in descending order.

Sorting by multiple columns #

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

We’ll use the contacts table in the sample database:

The following example uses the ORDER BY clause to sort contacts by first name and last name:

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:

• First, sort the rows by first names in ascending order to make an initial result set.
• Then, sort the initial result set by the last name in descending order.

Output:

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.

Sorting by column positions #

You don’t need to specify the column names for sorting. If you prefer, you can use the positions of columns 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 to indicate that the data is missing, unknown, or not applicable.

For example, some countries have states, whereas other countries do not. To represent states 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)

Notice that it is NULLS, not NULL. There is an S after NULL.

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

Try it

Output:

Sorting by expressions #

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 #