Deduct from Record(s) on a "First in, First Out" Basis when multiple records created simultaneously - How does SOQL handle this with ORDER BY?

Question

Background: My business performs remote training appointments with our customers, and they can be scheduled in any increment of 15 minutes. We track/deduct minutes from a lump-sum field on the Account object called "Used Training Minutes" by scheduling Events in 15 minute increments and updating the Used Minutes field when those events are Completed.

Recently our business need has changed. We can no longer use the lump-sum to just track the total number of training minutes that a customer has available to them, because now our education team wants those minutes to expire X days after purchase. So, we needed to track all purchased training separately, and I've created an object to store that.

In the new design, an event should be marked as Completed at which point we would get a list of all un-expired Training_History__c (Custom Object) records that are tied to that account, get the OLDEST one (First in), and deduct from those minutes until there are none left on that record (First out). If by chance the training was 60 minutes but the oldest Training_History__c record only had 30 minutes remaining on it, we still want to deduct those 30 (First out), and then move on to the SECOND oldest (which is now the 'first in with remaining minutes > 0') record and deduct the remaining 30 from that record, so on and so forth until we've deducted all of the minutes from the oldest unexpired records that still had minutes available.

Now, this question isn't about that logic - I think I have some ideas on how to make that work (but I have not started it yet), before I can even begin to work out that logic, I need to first tackle the problem of how to identify the "first in", specifically in a situation where multiple records were inserted simultaneously via Apex.

Problem: The customer might have purchased 2 or more different training products from us at the same time, which get synced into Salesforce from our billing system at the same time as well. Each training product should equal one Training_History__c record. Apex kicks off on the newly synced records which loops through them and builds a list of new Training_History__c records to insert for those trainings, and then inserts them all.

Of course, since these are all being inserted as a single list rather than hundreds of individual inserts, they all have the same exact CreatedDate time stamp, which (unless I'm mistaken) makes it impossible for me to get the "oldest" or "newest" one using a SOQL query with ORDER BY Field DESC/ASC

---

So I guess, ultimately, my question is : How does Salesforce handle ORDER BY when dealing with DateTime fields where the DateTime is identical between multiple records? Will they always return out of SOQL in the same order, or is the order subject to random changes since they all have the "same" CreatedDate DateTime stamp?

• IF they always get ordered in the same order in the list, then how is Salesforce determining that order of Ascension/Decending considering they all have the same TimeStamp? Would it use a combination of Time Stamp and Row Number and/or Record ID?
• IF the order is subject to change since they all have the same time stamp due to being inserted simultaneously, then how would I best identify the "oldest" record for use in my logic?

Answer 1

According to the Force.com SOQL and SOSL Reference on the ORDER BY clause:

You can use ORDER BY in a SELECT statement to control the order of the query results. There is no guarantee of the order of results unless you use an ORDER BY clause in a query.

So if you have multiple results where you do specify a field to ORDER BY, but they all have the same value, the same behavior applies: there is no guarantee of the order of results. You might find the following question relevant: SOQL result ordering in the absence of an Order By clause

If you want to get the most recently inserted records first, you can include Id as a fallback sort order for records where CreatedDate is the same.

ORDER BY CreateddDate DESC, Id DESC

Answer 2

The best way to order by insertion order and also avoid records that have the same ordering value (as can happen using CreatedDate) is to add an auto-number field and ORDER BY that (with or without DESC as appropriate).