ORDER BY clause for sorting query results - PolarDB

The ORDER BY clause sorts rows in a query result based on one or more expressions. Without it, the database returns rows in an unspecified order.

Syntax

ORDER BY expression [ ASC | DESC ] [, ...]

expression can be:

The name of an output column in the SELECT list
The ordinal number (left-to-right position) of an output column in the SELECT list
An arbitrary expression formed from input-column values, including columns not in the SELECT list

How it works

Rows are sorted by the first expression in the ORDER BY clause. When two rows are equal on that expression, the next expression is applied, and so on. If rows are equal on all specified expressions, they are returned in an implementation-dependent order.

Sort direction: Append ASC (ascending) or DESC (descending) after each expression. The default is ASC.

NULL values: Null values appear at the end of ascending results and at the beginning of descending results.

String collation: String data is sorted using the collation for the specific locale, which is set when the database cluster is initialized.

Limitations

When ORDER BY is applied to the result of a UNION, INTERSECT, or MINUS operation, the expression must be an output column name or ordinal number—arbitrary expressions are not allowed.
When an ORDER BY expression is a simple name that matches both an output column name and an input column name, ORDER BY treats it as the output column name. This is the opposite of GROUP BY behavior in the same situation, and is required for SQL standard compatibility.

Examples

Sort by column name or ordinal number

Both of the following queries sort results by the dname column. The first uses the column name; the second uses its ordinal number (2, because dname is the second column in the SELECT list).

SELECT * FROM dept ORDER BY dname;

SELECT * FROM dept ORDER BY 2;

Both return the same result:

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     40 | OPERATIONS | BOSTON
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
(4 rows)

Sort by a column not in the SELECT list

ORDER BY can reference columns that are not in the SELECT list. The following statement selects employee names but sorts by employee number:

SELECT ename FROM emp ORDER BY empno;