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.
Syntax
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.
Sorting
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
01-JAN-95).
01-JAN-95).
•
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.
Examples:
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
ORDER
BY 3;
Sorting by multiple columns:
SELECT
last_name, department_id, salary
FROM employees
ORDER
BY department_id, salary DESC;
