After a query has produced an output table (after the select list
has been processed) it can optionally be sorted. If sorting is not
chosen, the rows will be returned in random order. The actual
order in that case will depend on the scan and join plan types and
the order on disk, but it must not be relied on. A particular
output ordering can only be guaranteed if the sort step is explicitly
chosen.
The ORDER BY clause specifies the sort order:
SELECT select_list
FROM table_expression
ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...]
column1, etc., refer to select list
columns. These can be either the output name of a column (see
Section 4.3.2) or the number of a column. Some
examples:
SELECT a, b FROM table1 ORDER BY a;
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, sum(b) FROM table1 GROUP BY a ORDER BY 1;
As an extension to the SQL standard, PostgreSQL also allows ordering
by arbitrary expressions:
SELECT a, b FROM table1 ORDER BY a + b;
References to column names in the FROM clause that are
renamed in the select list are also allowed:
SELECT a AS b FROM table1 ORDER BY a;
But these extensions do not work in queries involving
UNION, INTERSECT, or EXCEPT,
and are not portable to other SQL databases.
Each column specification may be followed by an optional
ASC or DESC to set the sort direction to
ascending or descending. ASC order is the default.
Ascending order puts smaller values first, where
"smaller" is defined in terms of the
< operator. Similarly, descending order is
determined with the > operator.
If more than one sort column is specified, the later entries are
used to sort rows that are equal under the order imposed by the
earlier sort columns.
