Share via

OFFSET clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS 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, and ORDER BY to produce a deterministic result.

Note

When paging through a result set using LIMIT and 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.

Syntax

OFFSET integer_expression

Parameters

  • integer_expression

    A positive literal expression that returns an integer. If the expression is not foldable, is not of integer type, evaluates to NULL, or evaluates to a negative value, Azure Databricks raises INVALID_LIMIT_LIKE_EXPRESSION.

Common error conditions

Examples

> 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: INVALID_LIMIT_LIKE_EXPRESSION
  • Last updated on