0001 ---
0002 layout: global
0003 title: LIMIT Clause
0004 displayTitle: LIMIT 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 `LIMIT` clause is used to constrain the number of rows returned by
0025 the [SELECT](sql-ref-syntax-qry-select.html) statement. In general, this clause
0026 is used in conjunction with [ORDER BY](sql-ref-syntax-qry-select-orderby.html) to
0027 ensure that the results are deterministic.
0028
0029 ### Syntax
0030
0031 ```sql
0032 LIMIT { ALL | integer_expression }
0033 ```
0034
0035 ### Parameters
0036
0037 * **ALL**
0038
0039 If specified, the query returns all the rows. In other words, no limit is applied if this
0040 option is specified.
0041
0042 * **integer_expression**
0043
0044 Specifies a foldable expression that returns an integer.
0045
0046 ### Examples
0047
0048 ```sql
0049 CREATE TABLE person (name STRING, age INT);
0050 INSERT INTO person VALUES
0051 ('Zen Hui', 25),
0052 ('Anil B', 18),
0053 ('Shone S', 16),
0054 ('Mike A', 25),
0055 ('John A', 18),
0056 ('Jack N', 16);
0057
0058 -- Select the first two rows.
0059 SELECT name, age FROM person ORDER BY name LIMIT 2;
0060 +------+---+
0061 | name|age|
0062 +------+---+
0063 |Anil B| 18|
0064 |Jack N| 16|
0065 +------+---+
0066
0067 -- Specifying ALL option on LIMIT returns all the rows.
0068 SELECT name, age FROM person ORDER BY name LIMIT ALL;
0069 +-------+---+
0070 | name|age|
0071 +-------+---+
0072 | Anil B| 18|
0073 | Jack N| 16|
0074 | John A| 18|
0075 | Mike A| 25|
0076 |Shone S| 16|
0077 |Zen Hui| 25|
0078 +-------+---+
0079
0080 -- A function expression as an input to LIMIT.
0081 SELECT name, age FROM person ORDER BY name LIMIT length('SPARK');
0082 +-------+---+
0083 | name|age|
0084 +-------+---+
0085 | Anil B| 18|
0086 | Jack N| 16|
0087 | John A| 18|
0088 | Mike A| 25|
0089 |Shone S| 16|
0090 +-------+---+
0091
0092 -- A non-foldable expression as an input to LIMIT is not allowed.
0093 SELECT name, age FROM person ORDER BY name LIMIT length(name);
0094 org.apache.spark.sql.AnalysisException: The limit expression must evaluate to a constant value ...
0095 ```
0096
0097 ### Related Statements
0098
0099 * [SELECT Main](sql-ref-syntax-qry-select.html)
0100 * [WHERE Clause](sql-ref-syntax-qry-select-where.html)
0101 * [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
0102 * [HAVING Clause](sql-ref-syntax-qry-select-having.html)
0103 * [ORDER BY Clause](sql-ref-syntax-qry-select-orderby.html)
0104 * [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
0105 * [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
0106 * [DISTRIBUTE BY Clause](sql-ref-syntax-qry-select-distribute-by.html)
