OFFSET/FETCH Clause

The SQL Standard defines language for positioning a query at a fixed number of rows into the computed result (OFFSET) and then reading a fixed number of rows from that point onward (FETCH). For streaming queries, this means X number of rows as they are ordered by ROWTIME. For queries against foreign tables, this means X number of unordered results or results as X number of results ordered by an ORDER BY clause.

Note: When querying a Microsoft SQL Server foreign table, you must use an ORDER BY clause. Queries against a SQL Server foreign table that lack an ORDER BY clause will fail with an error.

Syntax

<result-offset-clause> ::= OFFSET <offset-row-count> { ROWS | ROW }
<fetch-first-clause> ::= FETCH { FIRST | NEXT } [ <fetch-first-quantity> ] { ROW | ROWS } ONLY
<offset-row-count> ::= integer
<fetch-first-quantity> ::= integer

With an OFFSET or FETCH clause included, SELECT behaves as follows:

  • OFFSET The default <offset-row-count> is 0. <offset-row-count> cannot be less than 0 or s-Server raises an exception.
  • FETCH If a FETCH clause is present, but <fetch-first-quantity> is omitted, then it defaults to 1. Otherwise, <fetch-first-quantity> is infinity. <fetch-first-quantity> cannot be less than 1, or s-Server raises an exception.

Note: The first <offset-row-count> rows of the query result are thrown away. The query returns the next <fetch-first-quantity> rows. Without an accompanying ORDER BY clause, the results are not well-defined.

Note: Errors can occur while processing rows. These errors do not kill the SELECT statement, but can result in discarded rows, with messages logged to the trace log. Discarded rows do not count toward satisfying either the <offset-row-count> positioning or the <fetch-first-quantity> limit.

Streams are inherently ordered by ROWTIME, so s-Server will always deliver meaningful results when you use OFFSET or FETCH on a streaming query.

For foreign tables, s-Server does not deliver query results in an order unless you use ORDER BY. This means that if you use different OFFSET values for different subsets of a query result, OFFSET may produce unpredictable results. Without ORDER BY, using FETCH produces similarly unpredictable results. For example, “rows 40 through 100” is only meaningful if rows 1-100 are in an order.

Examples

Data for examples:

a b
1 100
2 99
3 98
4 97
5 96
6 95
7 94

OFFSET and FETCH

SELECT * from "my_table" order by a offset 3 rows fetch next 2 rows only;

Result:

+----+-----+
| A  |  B  |
+----+-----+
| 4  | 97  |
| 5  | 96  |
+----+-----+

OFFSET Only

SELECT * from "my_table" order by a offset 3 rows;

Result:

+----+-----+
| A  |  B  |
+----+-----+
| 4  | 97  |
| 5  | 96  |
| 6  | 95  |
| 7  | 94  |
+----+-----+

FETCH Only

SELECT * from "my_table" order by a fetch next 2 rows only;

Result:

+----+------+
| A  |  B   |
+----+------+
| 1  | 100  |
| 2  | 99   |
+----+------+