ROWS_OFFSET (SELECT Option)
ROWS_OFFSET causes the query engine to discard the rows before the specified offset integer, which reduces the returned data set.
The collection of rows returned begins with the row specified by the offset integer. For example, if you include the option ROWS_OFFSET=5, the returned rows excludes the first 4 and begins with row 5.
Note: For a discussion of how this option, MAX_ROWS_LIMIT, OFFSET, FETCH and the maxRows JDBC/ODBC parameter work together, see <xHyperLink>“MAX_ROWS_LIMIT (SELECT Option)” on page 182.
Operator
SELECT
Syntax
ROWS_OFFSET=<int>
The <int> argument specifies the number of rows to discard from the returned data set.
Remarks
• You can combine this option with MAX_ROWS_LIMIT to return a restricted set of rows.
• A query should not use the ROWS_OFFSET option with OFFSET/FETCH pagination.
• For better performance with row filtering, use OFFSET and FETCH rather than MAX_ROWS_LIMIT and ROWS_OFFSET. The reason is that OFFSET and FETCH are SQL-standard options that are pushed to the data source for pass-through queries, while MAX_ROWS_LIMIT and ROWS_OFFSET are TDV-only constructs that always perform filtering in TDV (after a much larger number of rows may have been fetched).
• Refer to the SQL 2008 standard for syntax and usage of OFFSET and FETCH.
Example
SELECT {OPTION ROWS_OFFSET=10, MAX_ROWS_LIMIT=25} ID, Details
FROM tableZ order by ID