Order By Statement in Oracle

Posted by AbundantCode

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:

Tags: