How can I filter a SOQL query by the object's row index when there isn't such column?

Question

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.

Answer 1

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.