LIMIT / FETCH (2024)

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.

See Also

Working with account editions | Snowflake DocumentationSnowflake Stages 101—A Comprehensive Guide for Data Loading & Unloading (2024)Healthcare, HIPAA, and Data SharingIs Every Snowflake Actually Unique?

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 theyreceive 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 JDBCdriver) 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 thehuman 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 n and LIMIT count are equivalent.

Examples¶

The following examples show the effect of LIMIT. For simplicity, thesequeries omit the ORDER BY clause and assume that the output order isalways the same as shown by the first query. Real-world queries shouldinclude 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

See Also

Snowflake Community

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