The syntax of the optional ORDER
BY
clause is as follows:
ORDER BY expression [ ASC | DESC ] [, ...]
expression
can be the name or ordinal number of an output column in the SELECT
list. It can also be an arbitrary expression formed from input-column values.
The ORDER BY
clause causes the result rows to be sorted according to the specified expressions.
If two rows are equal according to the leftmost expression, they are compared according
to the next expression. If they are equal according to all 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 enables sorting based on 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 arbitrary expressions in the ORDER
BY
clause, including columns that do not appear in the SELECT
output list. Therefore, the following statement is valid:
SELECT ename FROM emp ORDER BY empno;
An ORDER BY
clause applying to the result of a UNION
, INTERSECT
, or MINUS
clause can specify only an output column name or number rather than an expression.
If an ORDER BY
expression is a simple name that matches both an output column name and an input
column name, ORDER BY
interprets it as the output column name. This is the opposite of the choice 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 any expression
in the ORDER BY
clause. If you specify neither ASC nor DESC, ASC is used.
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 based on the sorting rule set for specific regions created when the database cluster is initialized.
Examples
The following two examples show how to sort the results based on 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)