OFFSET and FETCH
When a table is sorted (preferably using ORDER BY on a primary key), OFFSET can be used to skip a specified number of rows. OFFSET is usually combined with FETCH NEXT value ROWS ONLY to support pagination, selecting a specific subset of rows in a table sorted on a primary key.
Note: For a discussion of how this option, MAX_ROWS_LIMIT, OFFSET, FETCH and the maxRows JDBC/ODBC parameter work together, see
MAX_ROWS_LIMIT (SELECT Option).
Syntax
SELECT *
FROM /table_path/table_name
ORDER BY column_name_PK
OFFSET value1 ROWS FETCH NEXT value2 ROWS ONLY
In the syntax, column_name_PK is a primary key that ensures consistent table ordering, value1 is the number of rows to skip, and value2 is the number of rows to fetch from the source.
Remarks
It is recommended that OFFSET be used with ORDER BY on a primary key to ensure repeatability for display of reliable subsets for paginated display of desired rows. The sorting with ORDER BY can be performed on any column, but if the table is changing rapidly, the ordering cannot be guaranteed. Tables that change in a more predictable manner might be safe to sort on any column with acceptably consistent output.
This function only applies to the top-level SELECT, and the result set from a query specifying OFFSET and FETCH is executed independently of other invocations.
Note: OFFSET and FETCH should not be used in a TDV view.
Example
SELECT orderdetails.OrderDetailID,
orderdetails.OrderID,
orderdetails.ProductID,
orderdetails.Status,
FROM /shared/examples/ds_orders/orderdetails
ORDER BY OrderDetailID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
In this example, OrderDetailID is a primary key, and the OFFSET line tells the query engine to skip the first 10 rows and return the next 10.