0001 ---
0002 layout: global
0003 title: ORDER BY Clause
0004 displayTitle: ORDER BY Clause
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `ORDER BY` clause is used to return the result rows in a sorted manner
0025 in the user specified order. Unlike the [SORT BY](sql-ref-syntax-qry-select-sortby.html)
0026 clause, this clause guarantees a total order in the output.
0027
0028 ### Syntax
0029
0030 ```sql
0031 ORDER BY { expression [ sort_direction | nulls_sort_oder ] [ , ... ] }
0032 ```
0033
0034 ### Parameters
0035
0036 * **ORDER BY**
0037
0038 Specifies a comma-separated list of expressions along with optional parameters `sort_direction`
0039 and `nulls_sort_order` which are used to sort the rows.
0040
0041 * **sort_direction**
0042
0043 Optionally specifies whether to sort the rows in ascending or descending
0044 order. The valid values for the sort direction are `ASC` for ascending
0045 and `DESC` for descending. If sort direction is not explicitly specified, then by default
0046 rows are sorted ascending.
0047
0048 **Syntax:** [ ASC `|` DESC ]
0049
0050 * **nulls_sort_order**
0051
0052 Optionally specifies whether NULL values are returned before/after non-NULL values. If
0053 `null_sort_order` is not specified, then NULLs sort first if sort order is
0054 `ASC` and NULLS sort last if sort order is `DESC`.
0055
0056 1. If `NULLS FIRST` is specified, then NULL values are returned first
0057 regardless of the sort order.
0058 2. If `NULLS LAST` is specified, then NULL values are returned last regardless of
0059 the sort order.
0060
0061 **Syntax:** `[ NULLS { FIRST | LAST } ]`
0062
0063 ### Examples
0064
0065 ```sql
0066 CREATE TABLE person (id INT, name STRING, age INT);
0067 INSERT INTO person VALUES
0068 (100, 'John', 30),
0069 (200, 'Mary', NULL),
0070 (300, 'Mike', 80),
0071 (400, 'Jerry', NULL),
0072 (500, 'Dan', 50);
0073
0074 -- Sort rows by age. By default rows are sorted in ascending manner with NULL FIRST.
0075 SELECT name, age FROM person ORDER BY age;
0076 +-----+----+
0077 | name| age|
0078 +-----+----+
0079 |Jerry|null|
0080 | Mary|null|
0081 | John| 30|
0082 | Dan| 50|
0083 | Mike| 80|
0084 +-----+----+
0085
0086 -- Sort rows in ascending manner keeping null values to be last.
0087 SELECT name, age FROM person ORDER BY age NULLS LAST;
0088 +-----+----+
0089 | name| age|
0090 +-----+----+
0091 | John| 30|
0092 | Dan| 50|
0093 | Mike| 80|
0094 | Mary|null|
0095 |Jerry|null|
0096 +-----+----+
0097
0098 -- Sort rows by age in descending manner, which defaults to NULL LAST.
0099 SELECT name, age FROM person ORDER BY age DESC;
0100 +-----+----+
0101 | name| age|
0102 +-----+----+
0103 | Mike| 80|
0104 | Dan| 50|
0105 | John| 30|
0106 |Jerry|null|
0107 | Mary|null|
0108 +-----+----+
0109
0110 -- Sort rows in ascending manner keeping null values to be first.
0111 SELECT name, age FROM person ORDER BY age DESC NULLS FIRST;
0112 +-----+----+
0113 | name| age|
0114 +-----+----+
0115 |Jerry|null|
0116 | Mary|null|
0117 | Mike| 80|
0118 | Dan| 50|
0119 | John| 30|
0120 +-----+----+
0121
0122 -- Sort rows based on more than one column with each column having different
0123 -- sort direction.
0124 SELECT * FROM person ORDER BY name ASC, age DESC;
0125 +---+-----+----+
0126 | id| name| age|
0127 +---+-----+----+
0128 |500| Dan| 50|
0129 |400|Jerry|null|
0130 |100| John| 30|
0131 |200| Mary|null|
0132 |300| Mike| 80|
0133 +---+-----+----+
0134 ```
0135
0136 ### Related Statements
0137
0138 * [SELECT Main](sql-ref-syntax-qry-select.html)
0139 * [WHERE Clause](sql-ref-syntax-qry-select-where.html)
0140 * [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
0141 * [HAVING Clause](sql-ref-syntax-qry-select-having.html)
0142 * [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
0143 * [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
0144 * [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
0145 * [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
