Offset: Offset is used to skip the specified number of records and fetch the other records from database while fetching the data using SOQL query.
For example list<Lead> lstLead = [select id,name from Lead offset 5 limit 10];
The above query skips first 5 records and fetches records from 6 to 15 from Database as we have limit 10.
We usually use the concept of offset in pagination.
Example:
Run the following piece of code and observe the difference in the SOQL queries.
List<lead> lstLead = [select id,name from Lead];
system.debug('Leads count with out Offset:'+lstLead.size());
lstLead = [select id,name from Lead offset 10];
system.debug('Leads count with out Offset:'+lstLead.size());
Output:
Considerations while using OFFSET:
1) We can use maximum offset as 2000 only.If we give offset value > 2000,results in NUMBER_OUTSIDE_VALID_RANGE error.
2) We cannot use offset in subquery in general,but in one scenario only,we can use it in subquery i.e if the parent query has Limit 1 clause,then we can use offset in subquery.
Example:
SELECT Name, Id
(
SELECT Name FROM Opportunity LIMIT 10 OFFSET 2
)
FROM Account
ORDER BY Name
LIMIT 1
The above query is valid since parent Account query has Limit 1 clause.
SELECT Name, Id
(
SELECT Name FROM Opportunity LIMIT 10 OFFSET 2
)
FROM Account
ORDER BY Name is invalid because offset is referred in subquery but in parent account query limit 1 is not used.
3) 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.
