LIMIT and OFFSET clauses

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

The LIMIT and OFFSET clauses retrieves and skips the number of rows specified in the number.

The LIMIT and OFFSET clauses are fully supported in Snowflake.

Grammar Syntax

[ LIMIT { number | ALL } ]
[ OFFSET start ]

Sample Source Patterns

LIMIT number

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT 5;

Results

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000.00
101	John	100	90000.00
102	Jorge	101	95000.00
103	Kwaku	101	105000.00
104	Paulo	102	110000.00

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT 5;

Results

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000.00
101	John	100	90000.00
102	Jorge	101	95000.00
103	Kwaku	101	105000.00
104	Paulo	102	110000.00

LIMIT ALL

Input Code:

Redshift

IN -> Redshift_02.sql

SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
LIMIT ALL;

Results

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000.00
101	John	100	90000.00
102	Jorge	101	95000.00
103	Kwaku	101	105000.00
104	Paulo	102	110000.00
105	Richard	102	85000.00
106	Mateo	103	95000.00
107	Liu	103	108000.00
108	Zhang	104	95000.00

Output Code:

Snowflake

OUT -> Redshift_02.sql

CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL;

Results

ID	NAME	MANAGER_ID	SALARY
100	Carlos		120000.00
101	John	100	90000.00
102	Jorge	101	95000.00
103	Kwaku	101	105000.00
104	Paulo	102	110000.00
105	Richard	102	85000.00
106	Mateo	103	95000.00
107	Liu	103	108000.00
108	Zhang	104	95000.00

OFFSET without LIMIT

Snowflake doesn't support OFFSET without LIMIT. The LIMIT is added after transformation with NULL, which is the default LIMIT.

Input Code:

Redshift

IN -> Redshift_03.sql

SELECT id, name, manager_id, salary
INTO limited_employees
FROM employee
OFFSET 5;

Results

ID	NAME	MANAGER_ID	SALARY
105	Richard	102	85000.00
106	Mateo	103	95000.00
107	Liu	103	108000.00
108	Zhang	104	95000.00

Output Code:

Snowflake

OUT -> Redshift_03.sql

CREATE TABLE IF NOT EXISTS limited_employees AS
SELECT id, name, manager_id, salary
FROM
employee
LIMIT NULL
OFFSET 5;

Results

ID	NAME	MANAGER_ID	SALARY
105	Richard	102	85000.00
106	Mateo	103	95000.00
107	Liu	103	108000.00
108	Zhang	104	95000.00

Known Issues

There are no known issues.

Related EWIs.

There are no related EWIs.