This topic describes the ORDER BY clause and how to use this clause.
The optional ORDERBY
clause has the following syntax:
ORDER BY expression [ ASC | DESC ] [, ...]
expression
can be the name or the ordinal number of an output column in the SELECT
list. It can also be an arbitrary expression that is formed from input-column values.
The ORDER BY
clause enables the result rows to be sorted based on the specified expressions. If
two rows are equal based on the leftmost expression, they are compared based on the
next expression. If they are equal based on all the specified expressions, they are
returned in an implementation-dependent order.
The ordinal number refers to the ordinal (left-to-right) position of the result column.
This feature defines a sorting by using a column that does not have a unique name.
This is not necessary because you can use the AS
clause to assign a name to a result column.
You can also use an expression in the ORDERBY
clause, including columns that do not appear in the SELECT
result list. Therefore, the following statement is valid:
SELECT ename FROM emp ORDER BY empno;
An ORDER BY
clause applied to the result of a UNION
, INTERSECT
, or MINUS
clause can specify only an output column name or number rather than an expression.
This is the limit of that feature.
If an ORDER BY
expression is a simple name that matches both output and input column names, ORDER BY
interprets the simple name as the output column name. This is the opposite of the
choice that is made by the GROUP BY
clause in the same situation. This inconsistency is made to be compatible with the
SQL standard.
You can add the ASC (ascending) or DESC (descending) keyword after an expression in
the ORDER BY
clause. If you do not specify ASC or DESC, ASC is used by default.
The null value is sorted in a higher order than other values. In other words, null values are at the end of an ascending order and are at the beginning of a descending order.
String data is sorted by using the collation for the specific locale. The collation is created when the database cluster is initialized.
Examples
The following two examples show how to sort the results by using the content of the second column (dname):
SELECT * FROM dept ORDER BY dname;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
40 | OPERATIONS | BOSTON
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
(4 rows)
SELECT * FROM dept ORDER BY 2;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
40 | OPERATIONS | BOSTON
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
(4 rows)