SQL Statement - Sorting rows using the ORDER BY clause

The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition.


          SELECT        expr
       FROM                table
       [WHERE              condition(s)]
       [ORDER BY    {column, expr, numeric_position} [ASC|DESC]];
In the syntax:
       ORDER BY            specifies the order in which the retrieved rows are displayed
       ASC                 orders the rows in ascending order (this is the default order)
       DESC                orders the rows in descending order

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.

Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

The default sort order is ascending:
        Numeric values are displayed with the lowest values first (for example, 1 to 999).
        Date values are displayed with the earliest value first (for example, 01-JAN-92 before
        Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).
        Null values are displayed last for ascending sequences and first for descending sequences.
        You can also sort by a column that is not in the SELECT list.

1.       To reverse the order in which the rows are displayed, specify the DESC keyword after the column name in the ORDER BY clause.
2.       You can also use a column alias in the ORDER BY clause.
3.       You can sort query results by specifying the numeric position of the column in the SELECT clause.
4.       You can sort query results by more than one column. The sort limit is the number of columns in the given table. In the ORDER BY clause, specify the columns and separate the column names using commas. If you want to reverse the order of a column, specify DESC after its name.

Sorting in descending order:

SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date DESC ;

Sorting by column alias:
SELECT employee_id, last_name, salary*12 annsal
FROM   employees
ORDER BY annsal ;

Sorting by using the column’s numeric position:
SELECT   last_name, job_id, department_id, hire_date
FROM     employees

Sorting by multiple columns:
SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;