LIMIT and OFFSET clauses

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.