Print this page

Workaround for Offset 2000 limit on SOQL Query

Knowledge Article Number 000232691
Description Using the standard query pagination, you can get the error, "The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 will result in a NUMBER_OUTSIDE_VALID_RANGE error." While we can't increase the limit since it's hard coded, here are some suggested workarounds.

QueryMore is the appropriate means of accomplishing this if you're getting data all at once. For pagination, such as in a website or portal, sort by some value then use filters. It's recommended to use a field with a high cardinality, many unique values, because it makes pagination that much easier.

If you're sorting by CreatedDate or ID. Your first query would look like this:

SELECT Id, Name, CreatedDate FROM Account ORDER BY CreatedDate LIMIT 2000

At this point, you've got 2000 records to work with. Take the 2000th ID, and add it to your next query:

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate > "Last-Returned-Created-Date" ORDER BY CreatedDate LIMIT 2000

Then, you'll get your next 2000 records. You can rinse and repeat as necessary. If you want to go back a page, reverse the order, but you'll also have to reverse the results:

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate < "First-Returned-Created-Date" ORDER BY CreatedDate DESC LIMIT 2000

For better performance, consider client-side caching with a periodic refresh of the data using "getUpdated()" and "getDeleted()" (SOAP API replication).

In any case, you can't just "go" to the 50,000th record initially; you have to step your way there. Caching would provide the biggest benefit in this regards and should be considered if you have large data sets.

Alternatively, you could also write a custom web service to quickly step through records using the method described above:

global webservice Id findOffset(String query) {
    SObject[] results = Database.query(query);
    return results[results.size()-1].Id;

Since Apex Code has a limit of 50,000 records, you can skip 50,000 rows at a time using this technique, bringing your total efficiency up to 1 API call per 50,000 rows, plus one for the final query that gives you the results for the page.

promote demote