LIMIT and OFFSET

Description

The parameter count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.

Click here to navigate to the PostgreSQL documentation page for this syntax.

Grammar Syntax

LIMIT { count | ALL }
OFFSET start

When The ALL clause is present it is translated to a NULL as shown in Snowflake documentation to ensure the same output

Sample Source Patterns

Setup Data

CREATE OR REPLACE table table1(
col1 int,
col2 string
);

insert into table1 values(1, 'first');
insert into table1 values(2, 'second');
insert into table1 values(3, 'third');
insert into table1 values(4, 'fourth');
insert into table1 values(5, 'fifth');

PostgreSQL

Limit with Offset

Code:

SELECT * FROM table1 LIMIT 2 OFFSET 2

Result:

COL1	COL2
3	third
4	fourth

Limit ALL

Code:

SELECT * FROM table1 LIMIT ALL OFFSET 2

Result:

COL1	COL2
3	third
4	fourth
5	fifth

Snowflake

Limit with Offset

Code:

SELECT * FROM table1 LIMIT 2 OFFSET 2

Result:

COL1	COL2
3	third
4	fourth

Limit ALL

Code:

SELECT * FROM table1 LIMIT NULL OFFSET 2

Result:

COL1	COL2
3	third
4	fourth
5	fifth