Question about SOQL WHERE LIMIT effect query result's order

Question

We has an object with more 50000 records, in this object Name field is auto number. Display all data on the page no good experience for read, so we create a paging element split these records in serval pages, in our design we show 2000 records in on page.

When we test this page we found duplicated records display in diference page. After research we found in SOQL same record in LIMIT 2000 and LIMIT 4000 will have different index.

This is our test code:

Integer index1 = 0;
for(Member__c member : [SELECT ID FORM Member__c WHERE Industry__c = 'Bank' OR Industry__c = 'Stock' OR Type__c = 'Futures' LIMIT 2000]) { 
    if(String.valueOf(member.Id) == 'a0D5e00000EXpOmEAL') { 
        break;
    }
    index1++;
}
System.debug('LIMIT 2000');
System.debug(index1);
Integer index2 = 0;
for(Member__c member : [SELECT ID FORM Member__c WHERE Industry__c = 'Bank' OR Industry__c = 'Stock' OR Type__c = 'Futures' LIMIT 4000]) { 
    if(String.valueOf(member.Id) == 'a0D5e00000EXpOmEAL') { 
        break;
    }
    index1++;
}
System.debug('LIMIT 4000');
System.debug(index2);

In Apex Log we get follow result:

LIMIT 2000

1248

LIMIT 4000

2249

Then we change LIMIT 2000 to LIMIT 6000, the Log result as follow:

LIMIT 6000

2249

LIMIT 4000

2249

Also we found if we remove WHERE seen this issue disappeared. This issue only observed with SOQL query with WHERE. And if we query all records at once, we can't reproduced this issue too.

We been search Salesforce SOQL Help document by serval days, we don't found any detail about how SOQL result order, and how LIMIT and WHERE effect query result's order. I want to ask you about does Salesforce has such document? Is Limit 4000 some kind of special value in Salesforce? Why WHERE effect query's order?

Answer 1

I recommend first adding an ORDER BY Clause after the Where e.g.

SELECT ID FROM Member__c WHERE Industry__c = 'Bank' OR Industry__c = 'Stock' OR Type__c = 'Futures' ORDER BY CreatedDate DESC LIMIT 2000

Also,

• Your code snippet has "FORM" not "FROM"

If I am understanding correctly. The first query when it is less than the 2nd query it doesn't display duplicate recoords. But, when the first query is more than the 2nd it does (sometimes).

Adding an ORDER BY will force it to query results in an ordered array instead of how ever the hell it wants.

If you want some help with this, give me a call and I can help troubleshoot. Check bio for link.