3

More specifically, I want to create various individual SELECT queries in which I retrieve the first 1000 results of the object, than the next 1000 and so on. Example:

SELECT Id, Name FROM Account WHERE rowIndex BETWEEN 1 AND 1000;
SELECT Id, Name FROM Account WHERE rowIndex BETWEEN 1001 AND 2000;
(...)

The thing is, the column "rowIndex" (or an equivelent one) does not exist in the object (column "Id" does not qualify).

I know OFFSET could be an option but it's limited to 2000 records and I have around 100000.

So, how can I retrieve only 1000 results each time of the query: "SELECT Id, Name FROM Account"?

Thank you.

1
  • Generally in Salesforce, adding a field (an auto-number in this case) where necessary is the way to go.
    – Keith C
    Commented May 31, 2017 at 8:38

1 Answer 1

0

You can use Order By and where clause to fulfill your result. First you need to store your last record invariable and next time use that variable in query

for first time

List<Account> accList = [Select Id from Account Order BY ID];
accId = accList[accList .size() - 1].Id;

and Now in next query

List<Account> accList = [Select Id from Account WHERE ID >: accId  Order BY ID];
accId = accList[accList .size() - 1].Id;

You can also make a dynamic query to handle this in single place. Using this approach you can query millions of records unless you don't hit other limits.

4
  • Thank for your answer. But from what I can gather your are using another language besides SOQL (Apex?) and I don't have that option. I am using the Bulk API in which the job operation I am opening is of type "query". Commented May 31, 2017 at 11:54
  • @DiogoMagalhães This is SOQl but I think in your case you need to hardcode the Id. Commented May 31, 2017 at 12:25
  • That is not an option either, but thank you for this time to help me :) Commented May 31, 2017 at 13:46
  • @DiogoMagalhães, if you use ORDER BY Id in conjunction with LIMIT and OFFSET, you can page through hundreds of records at a time.
    – nbrown
    Commented Sep 15, 2020 at 19:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .