|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
5.5 Sorting Rows
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 an unspecified 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.
ORDER BY clause specifies the sort order:
SELECT select_list FROM table_expression ORDER BY sort_expression1 [ASC | DESC] [, sort_expression2 [ASC | DESC] ...]
The sort expression(s) can be any expression that would be valid in the query's select list. An example is
SELECT a, b FROM table1 ORDER BY a + b, c;
When more than one expression is specified,
the later values are used to sort rows that are equal according to the
earlier values. Each expression may be followed by an optional
DESC keyword 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
For backwards compatibility with the SQL92 version of the standard, a sort_expression can instead be the name or number of an output column, as in
SELECT a + b AS sum, c FROM table1 ORDER BY sum; SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
both of which sort by the first output column. Note that an output column name has to stand alone, it's not allowed as part of an expression--for example, this is not correct:
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
This restriction is made to reduce ambiguity. There is still
ambiguity if an
ORDER BY item is a simple name that
could match either an output column name or a column from the table
expression. The output column is used in such cases. This would
only cause confusion if you use
AS to rename an output
column to match some other table column's name.
ORDER BY can be applied to the result of a
combination, but in this case it is only permitted to sort by
output column names or numbers, not by expressions.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|