Force.com SOQL and SOSL Reference

Clouds with binary code floating aboveCloud with binary code floating above

No Results

Search Tips:

  • Please consider misspellings
  • Try different search keywords
PDF

Newer Version Available

This content describes an older version of this product. View Latest

OFFSET

When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause on a SOQL query. For example, you can use OFFSET to display records 51 to 75 and then jump to displaying records 301 to 350. Using OFFSET is an efficient way to handle large results sets.

Use OFFSET to specify the starting row offset into the result set returned by your query. Because 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. OFFSET is available in API version 24.0 and later.

1SELECT fieldList
2FROM objectType
3[WHERE conditionExpression] 
4ORDER BY fieldOrderByList
5LIMIT numberOfRowsToReturn
6OFFSET numberOfRowsToSkip

As an example, if a SOQL query normally returned 50 rows, you could use OFFSET 10 in your query to skip the first 10 rows:

1SELECT Name
2FROM Merchandise__c
3WHERE Price__c > 5.0
4ORDER BY Name
5LIMIT 100
6OFFSET 10

The result set for the preceding example would be a subset of the full result set, returning rows 11 through 50 of the full set.

Considerations When Using OFFSET

Here are a few points to consider when using OFFSET in your queries:

  • The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 will result in a NUMBER_OUTSIDE_VALID_RANGE error.
  • OFFSET is intended 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:
    1SELECT Name, Id
2FROM Merchandise__c
3WHERE Id IN 
4   (
5     SELECT Id
6     FROM Discontinued_Merchandise__c
7     LIMIT 100
8     OFFSET 20
9   )
10ORDER BY Name
    A sub-query can use OFFSET only if the parent query has a LIMIT 1 clause. The following query is a valid use of OFFSET in a sub-query:
    1SELECT Name, Id
2    (
3        SELECT Name FROM Opportunity LIMIT 10 OFFSET 2
4    )
5FROM Account
6ORDER BY Name
7LIMIT 1
    OFFSET cannot be used as a sub-query in the WHERE clause, even if the parent query uses LIMIT 1.

    Using OFFSET in sub-queries is a pilot feature that is subject to change in future releases, and is not intended for use in a production setting. There is no support associated with this pilot feature. For more information, contact Salesforce

    Note

  • We 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 will have a stable ordering, however the ordering key is subject to change and should not be relied on.
  • Similarly, we recommend using a LIMIT clause in combination with OFFSET if you need to retrieve subsequent subsets of the same result set. For example, you could retrieve the first 100 rows of a query using the following:
    1SELECT Name, Id
2FROM Merchandise__c
3ORDER BY Name
4LIMIT 100
5OFFSET 0
    You could then retrieve the next 100 rows, 101 through 201, using the following query:
    1SELECT Name, Id
2FROM Merchandise__c
3ORDER BY Name
4LIMIT 100
5OFFSET 100
  • 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. As an example, suppose the following query normally returns a full result set of 50 rows, and the first 10 rows are skipped using an OFFSET clause:
    1SELECT Name
2FROM Merchandise__c
3ORDER BY Name
4OFFSET 10
    After the query is run, 10 new rows are then inserted into Merchandise__c with Name values that come early in the sort order. If the query is run again, with the same OFFSET value, a different set of rows is skipped. If you need to query multiple pages of records with a consistent server-side cursor, use the queryMore() in SOAP API.
  • Offsets are not intended to be used as a replacement for using queryMore(), given the maximum offset size and lack of a server-side cursor. Multiple queries with offsets into a large result set will have a higher performance impact than using queryMore() against a server-side cursor.
  • 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.
  • 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.