Applies to: Databricks SQL SQL warehouse version 2022.35 or higher Databricks Runtime 11.2 and above
Skips a number of rows returned by a statement or subquery.
This clause is mostly used in the conjunction with LIMIT to page through a result set,
ORDER BY to produce a deterministic result.
When paging through a result set using
OFFSET the skipped rows still get processed.
These rows merely get suppressed from the result set.
Pagination with this technique is not advised for resource-intensive queries.
> CREATE TEMP VIEW person (name, age) AS VALUES ('Zen Hui', 25), ('Anil B' , 18), ('Shone S', 16), ('Mike A' , 25), ('John A' , 18), ('Jack N' , 16); -- Select the 4th and 5th rows by alphabetical order. > SELECT name, age FROM person ORDER BY name LIMIT 2 OFFSET 3; Mike A 25 Shone S 16 -- Specifying ALL option on LIMIT and an OFFSET of zero, returns all the rows. > SELECT name, age FROM person ORDER BY name LIMIT ALL OFFSET 0; Anil B 18 Jack N 16 John A 18 Mike A 25 Shone S 16 Zen Hui 25 -- A constant function expression as an input to OFFSET. > SELECT name, age FROM person ORDER BY name OFFSET length('SPARK'); Zen Hui 25 -- A non-literal expression as an input to OFFSET is not allowed. > SELECT name, age FROM person ORDER BY name OFFSET length(name); Error: The offset expression must evaluate to a constant value