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 值是在非 NULL 值之前/之後傳回。如果未指定
null_sort_order，則如果排序順序為ASC，NULL 會先排序，如果排序順序為DESC，NULL 會最後排序。- 如果指定
NULLS FIRST，則 NULL 值會先傳回，不論排序順序為何。 - 如果指定
NULLS LAST，則 NULL 值會最後傳回，不論排序順序為何。
語法：
[ 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|
+---+-----+----+