14

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.

  • Ids are only "reused" if you undelete a record. what's your use case for this? – superfell Mar 2 '13 at 17:44
  • 4
    Ids from permanently deleted records are not reused. – superfell Mar 2 '13 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 '13 at 17:38
  • I'll trade you for you describe your use case for needing insertion order. – superfell Mar 5 '13 at 1:08
  • also, depending on how you're doing inserts, the insert order may not exactly match the order that records are passed to create. – superfell Mar 5 '13 at 1:13
13

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.

  • 1
    Makes sense. Presumably there is no solution based on the "system" fields that are there by default? – Keith C Mar 2 '13 at 15:42
  • not that I'm aware of. All the system fields are dateTimes which have second precision as you mentioned. – Greg Grinberg Mar 2 '13 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 '15 at 5:07
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/

  • 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 '17 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 '17 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 '17 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 '17 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 '17 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.