16

Despite the large number of SOQL fragments that include "order by Id", my understanding is that "order by Id" does not guarantee that the records are returned in insertion order.

(Is it only after deletion/emptying that results in out of order IDs or are there other circumstances? Does anyone have insight into why the IDs are not in order?)

Also CreatedDate appears to to have a granularity of 1 second. So while "order by CreatedDate, Id" will work most of the time, if two records are created within the same second where one ID is being reused, again the order cannot be guaranteed.

Is there a solution that guarantees that records are returned in insertion order?

Note that this question has been edited after comments were made: it originally said that ids are reused.

14
  • Ids are only "reused" if you undelete a record. what's your use case for this?
    – superfell
    Mar 2, 2013 at 17:44
  • 4
    Ids from permanently deleted records are not reused.
    – superfell
    Mar 2, 2013 at 18:18
  • Can you comment about why "order by Id" does not appear to yield insertion order? What is your certainty about the reuse question based upon?
    – Keith C
    Mar 3, 2013 at 17:38
  • I'll trade you for you describe your use case for needing insertion order.
    – superfell
    Mar 5, 2013 at 1:08
  • 1
    Ids are allocated in chunks to various things that need to assign ids, these chunks are allocated sequentially but each thing using up its chunk may do so at a different rate. the chunks are also shared across org's so you might get id 1 and a user in a different org might get id 2 & 3, and your next id is id4. there are also a number of ways in which ids get assigned, but then are never seen, like rolled back transactions and other things. Whats your specific need for determining insertion order ?
    – superfell
    Mar 6, 2013 at 5:57

3 Answers 3

14

You can create an autonumber field on the object and order by that autonumber. Autonumber fields are number incrementors behind the scenes so they are not reused like Ids.

3
  • 1
    Makes sense. Presumably there is no solution based on the "system" fields that are there by default?
    – Keith C
    Mar 2, 2013 at 15:42
  • not that I'm aware of. All the system fields are dateTimes which have second precision as you mentioned. Mar 2, 2013 at 19:31
  • 1
    This answer is so elegantly simple. I'm elated and also feel dumb at the same time for not realizing this earlier. Thanks Greg!
    – Doug Ayers
    Nov 27, 2015 at 5:07
4

Despite the large number of SOQL fragments that include "order by Id", my understanding is that "order by Id" does not guarantee that the records are returned in insertion order.

Correct. I go in to more details here, but the gist of things is that a Salesforce instance, called a "pod", is comprised of various servers, and each server requests a "chunk" of Ids to assign out. Thus, two or more users on two or more servers performing inserts at the same time may end up both assigning sequential Ids relative to themselves, but out of order chronologically. You can only guarantee that Id values are increasing for a single user in a single transaction, once you start talking multi-transactional, all bets are out the window.

(Is it only after deletion/emptying that results in out of order IDs or are there other circumstances? Does anyone have insight into why the IDs are not in order?)

Id values are never reused, even if a record was deleted, was part of a unit test, was rolled back because of partial database saves, or a fatal exception/error occurs that rolls back the entire transaction. Those Id values are lost forever.

Also CreatedDate appears to to have a granularity of 1 second. So while "order by CreatedDate, Id" will work most of the time, if two records are created within the same second where one ID is being reused, again the order cannot be guaranteed.

Correct. The milliseconds isn't a guaranteed order. Generally speaking, records are by default ordered by CreatedDate and Id. Query optimizations may also cause records to shuffle around in order within this one second granularity, but is generally stable from user to user.

Is there a solution that guarantees that records are returned in insertion order?

As stated in other answers, an Auto-Number field will always be assigned in approximately insertion order. However, I'd like to add that, because of race conditions when many inserts happen in close proximity chronologically, there may still be edge cases (e.g. timing differences on how long a trigger takes to execute).

In an example script where I create a before insert trigger that waits X seconds before continuing on, I was able to prove that auto-number fields can also be out of order, even if some transactions started earlier.


Visualforce.remoting.buffer = false;
Promise.all(
    [8, 6, 4, 2].map(
        (delay) =>
            new Promise((resolve, reject) => {
                setTimeout(()=>{!$RemoteAction.q9158.insertRecord}(delay, (record) => resolve(record)),(9-delay)*250);
            })
    )
).then((results) => console.log(JSON.stringify(results)))

Output:

Id Start End Duration CreatedDate AutoNumber Delay
a0H1T00000MDSZWUA5 ...52565 ...60618 8053 ...60000 A-0083 8
a0H1T00000MDSZRUA5 ...53205 ...59343 6138 ...59000 A-0082 6
a0H1T00000MDSZMUA5 ...53560 ...57601 4041 ...57000 A-0081 4
a0H1T00000MDSZHUA5 ...54052 ...56087 2035 ...56000 A-0080 2

As you can see, with a ~500 millsecond delay between each record, but the first taking the longest amount of time, It ends up getting a higher auto-number value and Id. Granted, this is a contrived example, but it just goes to demonstrate that the order may be considered inaccurate if you care about which records started first.

If you want sub-second precision, and want a reasonable guarantee that the insertion order is "strictly" preserved, write a trigger to assign DateTime.now().getTime() to a number field for each record at the earliest possible moment. This would give you far greater accuracy than sorting by Id, CreatedDate, SystemModStamp, etc, barring potentially millisecond discrepancies between each server in a pod. It's the only way to get a "perfectly" preserved order.

0

I came upon this post, as I recently had to address a use-case for returning SOQL ordered by id. In the context of a test, it certainly appears that "order by id" returns records in a predictable order without resorting to the addition of an auto-number field. It appears that order is predictive, forward and backward, and regardless of whether records are inserted in an array, or discretely. Order of insert follows the order in which they are listed in the array.

Longer discussion here: http://www.bespokn.net/2017/03/03/order-by-id/

8
  • 1
    It looks that way yes until it is not. It is stated in the docs That Ids are not created in an ordered manner. Most of the time you will be fine until your are not and wished you had listened
    – Eric
    Mar 3, 2017 at 20:26
  • Ah.... but it is also stated in docs (a SOSL example in the link provided) that "This example orders the account names in ascending ID order". Linguistic types will take this to mean that there is some sense, or reason, for an "ordering by Id", and if it is not "order of creation", well then...it begs the question, What is it? I have to read this as Salesforce saying that you can put "ordering by Id" to some practical use. So, if Salesforce is denying this elsewhere... well, they're a big company, and they can do that. I guess that's what regression testing is for.
    – VeeDee
    Mar 4, 2017 at 14:22
  • order by Id is alpha order. If I say order by name that does not mean it is also ordered by creation date. Sometime when reading we read things into it when we should not
    – Eric
    Mar 4, 2017 at 14:31
  • here is the doc that specify it. developer.salesforce.com/docs/atlas.en-us.apexcode.meta/…. So you are ok as long as all records are created at the same time it would appear
    – Eric
    Mar 4, 2017 at 14:35
  • To me, the doc states more than what you have stated by way of interpretation. First of all, if it holds that records created in the same DML can be "ordered by id" and that the ids will be "sequential"... that is, one will follow logically from its predecessor... thus, if a2H<b2H && b2H<c2H, then a2H<c2H...and if Ids are not reused...then it follows that there will be a predictive sequencing (but not unbroken ordering) of Ids across multiple discrete DML operations. I would argue that there must be predictive sequencing. For a method to assign Id, how else to know it has been used already?
    – VeeDee
    Mar 4, 2017 at 15:11

You must log in to answer this question.

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