LIMIT / FETCH¶

Syntax¶

SELECT ...
FROM ...
[ ORDER BY ... ]
LIMIT <count> [ OFFSET <start> ]
[ ... ]

SELECT ...
FROM ...
[ ORDER BY ... ]
[ OFFSET <start> ] [ { ROW | ROWS } ] FETCH [ { FIRST | NEXT } ] <count> [ { ROW | ROWS } ] [ ONLY ]
[ ... ]

Parameters¶

count

The number of rows returned. Must be a non-negative integer constant.

The values NULL, empty string (''), and $$$$ are also accepted and are treated as “unlimited”; this is useful primarily for connectors and drivers (such as the JDBC driver) if they receive an incomplete parameter list when dynamically binding parameters to a statement.

OFFSET start

The row number after which the limited/fetched rows are returned. Must be a non-negative integer constant.

If OFFSET is omitted, the output starts from the first row in the result set.

The values NULL, empty string ('') and $$$$ are also accepted and are treated as 0 (i.e. do not skip any rows); this is useful primarily for connectors and drivers (such as the JDBC driver) if they receive an incomplete parameter list when dynamically binding parameters to a statement.

ONLY

Optional keyword that does not affect the output. It is used for emphasis to the human reader.

Usage notes¶

• An ORDER BY clause is not required; however, without an ORDER BY clause, the results are non-deterministic because query results are not necessarily in any particular order. To control the results returned, use an ORDER BY clause.

• Top-K pruning can improve the performance of queries that include both LIMIT and ORDER BY clauses. For more information, see Top-K pruning for improved query performance.

• TOP n and LIMIT count are equivalent.

• Both the LIMIT clause and the SAMPLE clause return a subset of rows from a table. When you use the LIMIT clause, Snowflake returns the specified number of rows in the fastest way possible. When you use the SAMPLE clause, Snowflake returns rows based on the sampling method specified in the clause.

Examples¶

The following examples show the effect of LIMIT. For simplicity, these queries omit the ORDER BY clause and assume that the output order is always the same as shown by the first query. Real-world queries should include ORDER BY.

select c1 from testtable;

+------+
|   C1 |
|------|
|    1 |
|    2 |
|    3 |
|   20 |
|   19 |
|   18 |
|    1 |
|    2 |
|    3 |
|    4 |
| NULL |
|   30 |
| NULL |
+------+

select c1 from testtable limit 3 offset 3;

+----+
| C1 |
|----|
| 20 |
| 19 |
| 18 |
+----+

select c1 from testtable order by c1;

+------+
|   C1 |
|------|
|    1 |
|    1 |
|    2 |
|    2 |
|    3 |
|    3 |
|    4 |
|   18 |
|   19 |
|   20 |
|   30 |
| NULL |
| NULL |
+------+

select c1 from testtable order by c1 limit 3 offset 3;

+----+
| ID |
|----|
|  2 |
|  3 |
|  3 |
+----+

Copy

The following example demonstrates the use of NULLs to indicate

• No limit to the number of rows.

• Start at row 1 (do not skip any rows)

  CREATE TABLE demo1 (i INTEGER);
  INSERT INTO demo1 (i) VALUES (1), (2);
  

  Copy

  SELECT * FROM demo1 ORDER BY i LIMIT NULL OFFSET NULL;
  +---+
  | I |
  |---|
  | 1 |
  | 2 |
  +---+
  

  Copy

  SELECT * FROM demo1 ORDER BY i LIMIT '' OFFSET '';
  +---+
  | I |
  |---|
  | 1 |
  | 2 |
  +---+
  

  Copy

  SELECT * FROM demo1 ORDER BY i LIMIT $$$$ OFFSET $$$$;
  +---+
  | I |
  |---|
  | 1 |
  | 2 |
  +---+
  

  Copy