ORDER BY
描述
返回按用户指定顺序排序的结果行。 与 SORT BY 子句不同，此子句可保证输出的总序。
语法
ORDER BY { { ALL [ sort_direction] [ nulls_sort_oder ] } |
{ expression [ sort_direction ] [ nulls_sort_oder ] } [, ...] }
sort_direction
[ ASC | DESC ]
nulls_sort_order
[ NULLS FIRST | NULLS LAST ]参数
ALL
一个相当于按出现顺序在 SELECT 列表中指定所有表达式的速记。 如果指定了 sort_direction 或 nulls_sort_order，它们将应用于每个表达式。
expression
任意类型的表达式，用于建立结果返回时所采用的顺序。
如果该表达式为文本 INT 值，则会将其解释为选择列表中的某个列位置。
sort_direction
指定 order by 表达式的排序顺序。
- ASC 此表达式的排序方向为升序
- DESC 此表达式的排序方向为降序
如果未显式指定排序方向，则默认按升序对行排序。
nulls_sort_order
可选择指定是在非 NULL 值之前还是之后返回 NULL 值。 如果未指定 null_sort_order，则在排序顺序为 ASC 时，Null 排在前面；排序顺序为 DESC 时，Null 排在后面。
示例
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);
-- Comparison operator in `WHERE` clause.
SELECT name, age FROM person ORDER BY age;
+---------+-----------+
| name| age|
+---------+-----------+
| Mary| NULL|
+---------+-----------+
| Jerry| NULL|
+---------+-----------+
| John| 30|
+---------+-----------+
| Dan| 50|
+---------+-----------+
| Mike| 80|
+---------+-----------+
SELECT name, age FROM person ORDER BY age DESC;
+---------+-----------+
| name| age|
+---------+-----------+
| Mike| 80|
+---------+-----------+
| Dan| 50|
+---------+-----------+
| John| 30|
+---------+-----------+
| Mary| NULL|
+---------+-----------+
| Jerry| NULL|
+---------+-----------+
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|
+---------+-----------+----------+
SELECT * FROM person ORDER BY ALL ASC;
+---------+-----------+----------+
| id| name| age|
+---------+-----------+----------+
| 100| John| 30|
+---------+-----------+----------+
| 200| Mary| NULL|
+---------+-----------+----------+
| 300| Mike| 80|
+---------+-----------+----------+
| 400| Jerry| NULL|
+---------+-----------+----------+
| 500| Dan| 50|
+---------+-----------+----------+
SELECT name, age FROM person ORDER BY age NULLS LAST;
+---------+-----------+
| name| age|
+---------+-----------+
| John| 30|
+---------+-----------+
| Dan| 50|
+---------+-----------+
| Mike| 80|
+---------+-----------+
| Mary| NULL|
+---------+-----------+
| Jerry| NULL|
+---------+-----------+
SELECT name, age FROM person ORDER BY age DESC NULLS FIRST;
+---------+-----------+
| name| age|
+---------+-----------+
| Mary| NULL|
+---------+-----------+
| Jerry| NULL|
+---------+-----------+
| Mike| 80|
+---------+-----------+
| Dan| 50|
+---------+-----------+
| John| 30|
+---------+-----------+