0001 ---
0002 layout: global
0003 title: SORT BY Clause
0004 displayTitle: SORT 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 `SORT BY` clause is used to return the result rows sorted
0025 within each partition in the user specified order. When there is more than one partition
0026 `SORT BY` may return result that is partially ordered. This is different
0027 than [ORDER BY](sql-ref-syntax-qry-select-orderby.html) clause which guarantees a
0028 total order of the output.
0029
0030 ### Syntax
0031
0032 ```sql
0033 SORT BY { expression [ sort_direction | nulls_sort_order ] [ , ... ] }
0034 ```
0035
0036 ### Parameters
0037
0038 * **SORT BY**
0039
0040 Specifies a comma-separated list of expressions along with optional parameters `sort_direction`
0041 and `nulls_sort_order` which are used to sort the rows within each partition.
0042
0043 * **sort_direction**
0044
0045 Optionally specifies whether to sort the rows in ascending or descending
0046 order. The valid values for the sort direction are `ASC` for ascending
0047 and `DESC` for descending. If sort direction is not explicitly specified, then by default
0048 rows are sorted ascending.
0049
0050 **Syntax:** `[ ASC | DESC ]`
0051
0052 * **nulls_sort_order**
0053
0054 Optionally specifies whether NULL values are returned before/after non-NULL values. If
0055 `null_sort_order` is not specified, then NULLs sort first if sort order is
0056 `ASC` and NULLS sort last if sort order is `DESC`.
0057
0058 1. If `NULLS FIRST` is specified, then NULL values are returned first
0059 regardless of the sort order.
0060 2. If `NULLS LAST` is specified, then NULL values are returned last regardless of
0061 the sort order.
0062
0063 **Syntax:** `[ NULLS { FIRST | LAST } ]`
0064
0065 ### Examples
0066
0067 ```sql
0068 CREATE TABLE person (zip_code INT, name STRING, age INT);
0069 INSERT INTO person VALUES
0070 (94588, 'Zen Hui', 50),
0071 (94588, 'Dan Li', 18),
0072 (94588, 'Anil K', 27),
0073 (94588, 'John V', NULL),
0074 (94511, 'David K', 42),
0075 (94511, 'Aryan B.', 18),
0076 (94511, 'Lalit B.', NULL);
0077
0078 -- Use `REPARTITION` hint to partition the data by `zip_code` to
0079 -- examine the `SORT BY` behavior. This is used in rest of the
0080 -- examples.
0081
0082 -- Sort rows by `name` within each partition in ascending manner
0083 SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person SORT BY name;
0084 +--------+----+--------+
0085 | name| age|zip_code|
0086 +--------+----+--------+
0087 | Anil K| 27| 94588|
0088 | Dan Li| 18| 94588|
0089 | John V|null| 94588|
0090 | Zen Hui| 50| 94588|
0091 |Aryan B.| 18| 94511|
0092 | David K| 42| 94511|
0093 |Lalit B.|null| 94511|
0094 +--------+----+--------+
0095
0096 -- Sort rows within each partition using column position.
0097 SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person SORT BY 1;
0098 +--------+----+--------+
0099 | name| age|zip_code|
0100 +--------+----+--------+
0101 | Anil K| 27| 94588|
0102 | Dan Li| 18| 94588|
0103 | John V|null| 94588|
0104 | Zen Hui| 50| 94588|
0105 |Aryan B.| 18| 94511|
0106 | David K| 42| 94511|
0107 |Lalit B.|null| 94511|
0108 +--------+----+--------+
0109
0110 -- Sort rows within partition in ascending manner keeping null values to be last.
0111 SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age NULLS LAST;
0112 +----+--------+--------+
0113 | age| name|zip_code|
0114 +----+--------+--------+
0115 | 18| Dan Li| 94588|
0116 | 27| Anil K| 94588|
0117 | 50| Zen Hui| 94588|
0118 |null| John V| 94588|
0119 | 18|Aryan B.| 94511|
0120 | 42| David K| 94511|
0121 |null|Lalit B.| 94511|
0122 +----+--------+--------+
0123
0124 -- Sort rows by age within each partition in descending manner, which defaults to NULL LAST.
0125 SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age DESC;
0126 +----+--------+--------+
0127 | age| name|zip_code|
0128 +----+--------+--------+
0129 | 50| Zen Hui| 94588|
0130 | 27| Anil K| 94588|
0131 | 18| Dan Li| 94588|
0132 |null| John V| 94588|
0133 | 42| David K| 94511|
0134 | 18|Aryan B.| 94511|
0135 |null|Lalit B.| 94511|
0136 +----+--------+--------+
0137
0138 -- Sort rows by age within each partition in descending manner keeping null values to be first.
0139 SELECT /*+ REPARTITION(zip_code) */ age, name, zip_code FROM person SORT BY age DESC NULLS FIRST;
0140 +----+--------+--------+
0141 | age| name|zip_code|
0142 +----+--------+--------+
0143 |null| John V| 94588|
0144 | 50| Zen Hui| 94588|
0145 | 27| Anil K| 94588|
0146 | 18| Dan Li| 94588|
0147 |null|Lalit B.| 94511|
0148 | 42| David K| 94511|
0149 | 18|Aryan B.| 94511|
0150 +----+--------+--------+
0151
0152 -- Sort rows within each partition based on more than one column with each column having
0153 -- different sort direction.
0154 SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person
0155 SORT BY name ASC, age DESC;
0156 +--------+----+--------+
0157 | name| age|zip_code|
0158 +--------+----+--------+
0159 | Anil K| 27| 94588|
0160 | Dan Li| 18| 94588|
0161 | John V|null| 94588|
0162 | Zen Hui| 50| 94588|
0163 |Aryan B.| 18| 94511|
0164 | David K| 42| 94511|
0165 |Lalit B.|null| 94511|
0166 +--------+----+--------+
0167 ```
0168
0169 ### Related Statements
0170
0171 * [SELECT Main](sql-ref-syntax-qry-select.html)
0172 * [WHERE Clause](sql-ref-syntax-qry-select-where.html)
0173 * [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
0174 * [HAVING Clause](sql-ref-syntax-qry-select-having.html)
0175 * [ORDER BY Clause](sql-ref-syntax-qry-select-orderby.html)
0176 * [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
0177 * [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
0178 * [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)