Categories:: Query syntax

ORDER BY¶

Specifies an ordering of the rows of the result table from a SELECT list.

语法

按特定列排序

SELECT ...
  FROM ...
  ORDER BY orderItem [ , orderItem , ... ]
  [ ... ]

其中：

orderItem ::= { <column_alias> | <position> | <expr> } [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ]

按所有列排序

SELECT ...
  FROM ...
  ORDER BY ALL [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ]
  [ ... ]

参数

column_alias: Column alias appearing in the query block’s SELECT list.
position: Position of an expression in the SELECT list.
expr: Any expression on tables in the current scope.

{ ASC | DESC }

可以按升序（从低到高）或降序（从高到低）返回排序键的值。

默认值：ASC

NULLS { FIRST | LAST }

可以根据排序顺序（ASC 或 DESC），指定 NULL 值是在非 NULL 值之前还是之后返回。

默认值：取决于排序顺序（ASC 或 DESC）；有关详细信息，请参阅下面的使用说明

ALL

按 SELECT 列表中指定的所有列对结果进行排序。结果按列的出现顺序排序。

例如，假设 SELECT 列表包含：

SELECT col_1, col_2, col_3
  FROM my_table
  ORDER BY ALL;

The results are sorted first by col_1, then by col_2, and then by col_3.

Note

如果 SELECT 列表中的列使用聚合函数，则无法指定 ORDER BY ALL。

使用说明

所有数据都根据 ASCII 表中每个字符的数字字节值进行排序。支持 UTF-8 编码。
For numeric values, leading zeros before the decimal point and trailing zeros (0) after the decimal point have no effect on sort order.

When NULLS FIRST or NULLS LAST isn’t specified, the ordering of NULL values depends on the setting of the DEFAULT_NULL_ORDERING parameter and the sort order:
- When the sort order is ASC (the default) and the DEFAULT_NULL_ORDERING parameter is set to LAST (the default), NULL values are returned last. Therefore, unless specified otherwise, NULL values are considered to be higher than any non-NULL values.
- When the sort order is ASC and the DEFAULT_NULL_ORDERING parameter is set to FIRST, NULL values are returned first.
- When the sort order is DESC and the DEFAULT_NULL_ORDERING parameter is set to FIRST, NULL values are returned last.
- When the sort order is DESC and the DEFAULT_NULL_ORDERING parameter is set to LAST, NULL values are returned first.
The sort order isn’t guaranteed to be consistent for values of different data types in semi-structured data, such as an array that contains elements of different data types.
Top-K pruning can improve the performance of queries that include both LIMIT and ORDER BY clauses. For more information, see Top-K pruning for improved query performance.
An ORDER BY clause can be used at different levels in a query, such as in a subquery or inside an OVER() clause for a window function. An ORDER BY clause inside a subquery or an OVER() clause applies only in that context. For example, the ORDER BY clause in the following query orders results only within the subquery, not the outermost level of the query:
```
SELECT *
  FROM (
 SELECT branch_name
   FROM branch_offices
   ORDER BY monthly_sales DESC
   LIMIT 3
  );
```

在该示例中，在子查询中指定 ORDER BY 子句，因此子查询按月销售额的顺序返回名称。子查询中的 ORDER BY 子句不适用于外部查询。此查询返回月销售额排名前三的三个分支机构的名称，但不一定按月销售额排序。

排序的开销可能很高。如果希望对外部查询的结果进行排序，请仅在查询的顶层使用 ORDER BY 子句，而且，除非必要，否则应避免在子查询中使用 ORDER BY 子句。

Similarly, when ORDER BY and LIMIT (or FETCH) clauses are at different nesting levels, results can be unpredictable. For details and examples, see the LIMIT / FETCH usage notes.

示例

以下示例演示如何使用 ORDER BY 对结果进行排序：

按字符串值排序
按数值排序
NULLS 排序：放在最前或最后

按字符串值排序

以下示例按字符串值对结果进行排序：

SELECT column1
  FROM VALUES
    ('a'), ('1'), ('B'), (null), ('2'), ('01'), ('05'),
    (' this'), ('this'), ('this and that'), ('&'), ('%')
  ORDER BY column1;

+---------------+
| COLUMN1       |
%---------------%
|  this         |
| %             |
| &             |
| 01            |
| 05            |
| 1             |
| 2             |
| B             |
| a             |
| this          |
| this and that |
| NULL          |
+---------------+

按数值排序

以下示例按数值对结果进行排序：

SELECT column1
  FROM VALUES
    (3), (4), (null), (1), (2), (6),
    (5), (0005), (.05), (.5), (.5000)
  ORDER BY column1;

+---------+
| COLUMN1 |
%---------%
|    0.05 |
|    0.50 |
|    0.50 |
|    1.00 |
|    2.00 |
|    3.00 |
|    4.00 |
|    5.00 |
|    5.00 |
|    6.00 |
|    NULL |
+---------+

NULLS 排序：放在最前或最后¶

The following example configures all queries in the session to sort NULLS last by setting the DEFAULT_NULL_ORDERING parameter to LAST.

ALTER SESSION SET DEFAULT_NULL_ORDERING = 'LAST';

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1;

+---------+
| COLUMN1 |
%---------%
|       1 |
|       2 |
|       3 |
|    NULL |
|    NULL |
+---------+

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 DESC;

+---------+
| COLUMN1 |
%---------%
|    NULL |
|    NULL |
|       3 |
|       2 |
|       1 |
+---------+

以下示例通过在查询中指定 NULLS FIRST 来替换 DEFAULT_NULL_ORDERING 参数：

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 NULLS FIRST;

+---------+
| COLUMN1 |
%---------%
|    NULL |
|    NULL |
|       1 |
|       2 |
|       3 |
+---------+

The following example sets the DEFAULT_NULL_ORDERING parameter to FIRST to sort NULLS first:

ALTER SESSION SET DEFAULT_NULL_ORDERING = 'FIRST';

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1;

+---------+
| COLUMN1 |
%---------%
|    NULL |
|    NULL |
|       1 |
|       2 |
|       3 |
+---------+

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 DESC;

+---------+
| COLUMN1 |
%---------%
|       3 |
|       2 |
|       1 |
|    NULL |
|    NULL |
+---------+

以下示例通过在查询中指定 NULLS LAST 来替换 DEFAULT_NULL_ORDERING 参数：

SELECT column1
  FROM VALUES (1), (null), (2), (null), (3)
  ORDER BY column1 NULLS LAST;

+---------+
| COLUMN1 |
%---------%
|       1 |
|       2 |
|       3 |
|    NULL |
|    NULL |
+---------+

按 SELECT 列表中的所有列排序¶

要运行此部分中的示例，请创建下表：

CREATE OR REPLACE TABLE my_sort_example(a NUMBER, s VARCHAR, b BOOLEAN);

INSERT INTO my_sort_example VALUES
  (0, 'abc', TRUE),
  (0, 'abc', FALSE),
  (0, 'abc', NULL),
  (0, 'xyz', FALSE),
  (0, NULL, FALSE),
  (1, 'xyz', TRUE),
  (NULL, 'xyz', FALSE);

以下示例按表中的所有列对结果进行排序：

SELECT * FROM my_sort_example
  ORDER BY ALL;

As shown below, the results are sorted first by the a column, then by the s column, and then by the b column (the order in which the columns were defined in the table).

+------+------+-------+
| A    | S    | B     |
|------+------+-------|
| 0    | abc  | False |
| 0    | abc  | True  |
| 0    | abc  | NULL  |
| 0    | xyz  | False |
| 0    | NULL | False |
| 1    | xyz  | True  |
| NULL | xyz  | False |
+------+------+-------+

以下示例按升序对结果进行排序。

SELECT * FROM my_sort_example
  ORDER BY ALL ASC;

+------+------+-------+
| A    | S    | B     |
|------+------+-------|
| 0    | abc  | False |
| 0    | abc  | True  |
| 0    | abc  | NULL  |
| 0    | xyz  | False |
| 0    | NULL | False |
| 1    | xyz  | True  |
| NULL | xyz  | False |
+------+------+-------+

以下示例将 DEFAULT_NULL_ORDERING 参数设置为针对会话期间执行的所有查询将 NULL 值排在最后：

ALTER SESSION SET DEFAULT_NULL_ORDERING = 'LAST';

SELECT * FROM my_sort_example
  ORDER BY ALL;

+------+------+-------+
| A    | S    | B     |
|------+------+-------|
| NULL | xyz  | False |
| 0    | NULL | False |
| 0    | abc  | NULL  |
| 0    | abc  | False |
| 0    | abc  | True  |
| 0    | xyz  | False |
| 1    | xyz  | True  |
+------+------+-------+

以下示例在查询中指定 NULLS FIRST 以替换该设置：

SELECT * FROM my_sort_example
  ORDER BY ALL NULLS FIRST;

+------+------+-------+
| A    | S    | B     |
|------+------+-------|
| NULL | xyz  | False |
| 0    | NULL | False |
| 0    | abc  | NULL  |
| 0    | abc  | False |
| 0    | abc  | True  |
| 0    | xyz  | False |
| 1    | xyz  | True  |
+------+------+-------+

The following example returns the columns in the order b, s, and a. The results are sorted first by b, then by s, and then by a:

SELECT b, s, a FROM my_sort_example
  ORDER BY ALL NULLS LAST;

+-------+------+------+
| B     | S    | A    |
|-------+------+------|
| False | abc  | 0    |
| False | xyz  | 0    |
| False | xyz  | NULL |
| False | NULL | 0    |
| True  | abc  | 0    |
| True  | xyz  | 1    |
| NULL  | abc  | 0    |
+-------+------+------+