OFFSET:
Syntax:
Example:
Also Read: Pagination Using Offset
Before using OFFSET Go through this points:
OFFSET is used to specify the starting row offset into the result set returned by your query. Using OFFSET is helpful for paging into large result sets, in scenarios where you need to quickly jump to a particular subset of the entire results. As the offset calculation is done on the server and only the result subset is returned, using OFFSET is more efficient than retrieving the full result set and then filtering the results locally.
Syntax:
SELECT fieldList FROM objectType [WHERE conditionExpression] ORDER BY fieldOrderByList LIMIT number_of_rows_to_return OFFSET number_of_rows_to_skip
Example:
The SOQL query normally returned 100 rows, you could use OFFSET 20 in your query to skip the first 20 rows and returns 21th row to 120th rows.
SELECT Name FROM Account WHERE Industry = 'Media' ORDER BY Name LIMIT 100 OFFSET 20
Before using OFFSET Go through this points:
1. The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 will result in a NUMBER_OUTSIDE_VALID_RANGE error.
2. OFFSET is must to be used in a top-level query, and is not allowed in most sub-queries, so the following query is invalid and will return a MALFORMED_QUERY error.
SELECT Name, Id FROM Account WHERE Id IN ( SELECT Id FROM contact LIMIT 100 OFFSET 20 ) ORDER BY Name OFFSET 20
A sub-query can use OFFSET only if the parent query has a LIMIT 1 clause. Thsi is a pilot feature it is to change in future releases.
3. When using OFFSET, only the first batch of records will be 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.
4. 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.
3. When using OFFSET, only the first batch of records will be 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.
4. 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.