Snowflake LIMIT and OFFSET

You can use the LIMIT clause in Snowflake to limit the number of results that are returned by the SQL statement or subquery. The LIMIT clause allows you to test your SQL queries with the limited number of rows instead of executing it on the entire data set. In this article, we will check Snowflake LIMIT and OFFSET, syntax, uses and some examples.

The LIMIT clause constrains the maximum number of rows returned by a statement or subquery. It is a PostgreSQL syntax to limit the SELECT statement results. Snowflake also supports ANSI syntax such as FETCH to limit rows. It can accept one argument which is the number of rows returned.

Following is the syntax of Snowflake LIMIT and OFFSET.

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

An ORDER BY clause is not mandatory; however, without an ORDER BY clause, the results are non-deterministic because results within a result set are not necessarily in any particular order. You can use ORDER BY to control the result.

Snowflake LIMIT Clause

When you use the LIMIT clause with one argument, Snowflake will use this argument to determine the maximum number of rows to return from the first row of the result set.

For example, following query ensures only the first 3 results are returned.

SELECT *
FROM   some_table
LIMIT  3;

Snowflake LIMIT Clause Parameters

The LIMIT clause accepts one parameter count.

The parameter can be:

A NULL Value.
An empty string ('').
And $$$$

The LIMIT clause accepts all the above parameters and are treated as “unlimited”. i.e. return all rows.

Snowflake OFFSET Clause

You can also specify an OFFSET from where to start returning data. If you omit the OFFSET, the output starts from the first row in the result set. The OFFSET clause allows you to read rows from a table in a batches.

For example, say you want to get 5 rows, but not the first five. You want to get rows 3 through 8. You’ll want to add an OFFSET of 2 to skip the first two rows:

SELECT *
FROM
some_table
LIMIT 5 offset 2;

Snowflake OFFSET Clause Parameters

The OFFSET clause accepts one parameter start.

The parameter can be:

A NULL Value.
An empty string ('').
And $$$$

The LIMIT clause accepts all the above parameters and are treated as “0” i.e. do not skip any rows.

Snowflake OFFSET without LIMIT

As of now, LIMIT is mandatory to use OFFSET clause. However, you can set it to NULL to use OFFSET. In future, Snowflake may enhance this feature and allow OFFSET without LIMIT clause.

For example,

SELECT *
FROM
some_table
LIMIT NULL offset 2;