When expecting many records in a query’s results, we can display the results in multiple pages by using the OFFSET clause in a SOQL query. We can use OFFSET keyword in SOQL to specify the starting row from the result returned by the query. For example if there are 50 records then, if we specify offset as 20 in the query then it would return record 21 to 50, it will skip first 20 records.

In the following example the query will return rows 11 through 110, and will skip first 10 rows.

SELECT Name, AccountNumber, Type
FROM Account
WHERE Type = 'Customer - Direct'
ORDER BY Name
LIMIT 100
OFFSET 10

Considerations to Use OFFSET:

• The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGE error.
• A sub-query can use OFFSET only if the parent query has a LIMIT 1 clause.
• OFFSET cannot be used as a sub-query in the WHERE clause, even if the parent query uses LIMIT 1.
• Salesforce recommend using an ORDER BY clause when you use OFFSET to ensure that the result set ordering is consistent. The row order of a result set that does not have an ORDER BY clause has a stable ordering, however the ordering key is subject to change and should not be relied on.
• Also Salesforce recommend using a LIMIT clause with OFFSET if you need to retrieve subsequent subsets of the same result set.
• OFFSET clause can be used to implement pagination in visualforce page tables.
• OFFSET is applied to the result set returned at the time of the query. No server-side cursor is created to cache the full result set for future OFFSET queries. The page results may change if the underlying data is modified during multiple queries using OFFSET into the same result set.
• When using OFFSET, only the first batch of records are returned for a given query. If you want to retrieve the next batch, you’ll need to re-execute the query with a higher offset value.
• The OFFSET clause is allowed in SOQL used in SOAP API, REST API, and Apex. It’s not allowed in SOQL used within Bulk API or Streaming API.