Offset in Salesforce

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.