ORDER BY 子句
描述
ORDER BY 子句用于按照用户指定的顺序以排序方式返回结果行。与 SORT BY 子句不同，此子句保证输出的总体顺序。
语法
ORDER BY { expression [ sort_direction | nulls_sort_order ] [ , ... ] }
参数
-
ORDER BY
指定一个逗号分隔的表达式列表，以及可选参数
sort_direction和nulls_sort_order，用于对行进行排序。 -
sort_direction
可选地指定是按升序还是降序对行进行排序。排序方向的有效值为
ASC（升序）和DESC（降序）。如果未明确指定排序方向，则默认情况下按升序对行进行排序。语法： [ ASC
|DESC ] -
nulls_sort_order
可选地指定是在非空值之前/之后返回空值。如果未指定
null_sort_order，则如果排序顺序为ASC，则空值排在前面；如果排序顺序为DESC，则空值排在最后。- 如果指定了
NULLS FIRST，则无论排序顺序如何，空值都将首先返回。 - 如果指定了
NULLS LAST，则无论排序顺序如何，空值都将最后返回。
语法：
[ NULLS { FIRST | LAST } ] - 如果指定了
示例
CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
(100, 'John', 30),
(200, 'Mary', NULL),
(300, 'Mike', 80),
(400, 'Jerry', NULL),
(500, 'Dan', 50);
-- Sort rows by age. By default rows are sorted in ascending manner with NULL FIRST.
SELECT name, age FROM person ORDER BY age;
+-----+----+
| name| age|
+-----+----+
|Jerry|null|
| Mary|null|
| John| 30|
| Dan| 50|
| Mike| 80|
+-----+----+
-- Sort rows in ascending manner keeping null values to be last.
SELECT name, age FROM person ORDER BY age NULLS LAST;
+-----+----+
| name| age|
+-----+----+
| John| 30|
| Dan| 50|
| Mike| 80|
| Mary|null|
|Jerry|null|
+-----+----+
-- Sort rows by age in descending manner, which defaults to NULL LAST.
SELECT name, age FROM person ORDER BY age DESC;
+-----+----+
| name| age|
+-----+----+
| Mike| 80|
| Dan| 50|
| John| 30|
|Jerry|null|
| Mary|null|
+-----+----+
-- Sort rows in ascending manner keeping null values to be first.
SELECT name, age FROM person ORDER BY age DESC NULLS FIRST;
+-----+----+
| name| age|
+-----+----+
|Jerry|null|
| Mary|null|
| Mike| 80|
| Dan| 50|
| John| 30|
+-----+----+
-- Sort rows based on more than one column with each column having different
-- sort direction.
SELECT * FROM person ORDER BY name ASC, age DESC;
+---+-----+----+
| id| name| age|
+---+-----+----+
|500| Dan| 50|
|400|Jerry|null|
|100| John| 30|
|200| Mary|null|
|300| Mike| 80|
+---+-----+----+