+ Start a Discussion
SalesRedSalesRed 

Ordering SOQL by CreatedDate

Hi,

 

This may seem like a simple question but one I thought I'd ask to confirm all the same.

- if 2 records are inserted to an object within the same second,  if ordering the results of a SOQL query by createddate will Salesforce return the correct order in which they were inserted (if miliseconds can be the differentiator).

 

The reason I ask is I note the createddate field is of type datetime and it states on the following

http://www.salesforce.com/us/developer/docs/api/Content/primitive_data_types.htm#i1435039

Regular dateTime fields are full timestamps with a precision of one second. therefore I'm assuming it may not order the results correclty as per when they were inserted if they were created within the same second.

 

Thanks in advance for any further information on this.

Best Answer chosen by Admin (Salesforce Developers) 
Saurabh DhobleSaurabh Dhoble

Here's what I did -

 

1. Ran this code in the Anonymous code execution window -- this inserted two accounts, with "Account # 2" inserted after "Account # 1".

 

Account a = new Account();
a.Name = 'Account # 1';
insert a;

Account b = new Account();
b.Name = 'Account # 2';
insert b;

 

2. Then, I ran this query -

select Id, Name, CreatedDate from Account order by CreatedDate desc

 

To my surprise, Salesforce did not order the rows correctly - Account # 1 showed higher than Account # 2, even though they were ordered in descending order of create date.

 

Leads me to believe that when two records are created in SF in the same second, the ordering is not predictable.

 

All Answers

Saurabh DhobleSaurabh Dhoble

Here's what I did -

 

1. Ran this code in the Anonymous code execution window -- this inserted two accounts, with "Account # 2" inserted after "Account # 1".

 

Account a = new Account();
a.Name = 'Account # 1';
insert a;

Account b = new Account();
b.Name = 'Account # 2';
insert b;

 

2. Then, I ran this query -

select Id, Name, CreatedDate from Account order by CreatedDate desc

 

To my surprise, Salesforce did not order the rows correctly - Account # 1 showed higher than Account # 2, even though they were ordered in descending order of create date.

 

Leads me to believe that when two records are created in SF in the same second, the ordering is not predictable.

 

This was selected as the best answer
SalesRedSalesRed

Saurabh DhobleSaurabh Dhoble

It might, but I don't like playing around with timestamp fields - it's just unreliable to base decisions on that.

 

You might be better off by creating an auto-number field on your object. That way, each new object will be assigned a new value, and you can figure out the order of creation simply by ordering on this field.

sfdcfoxsfdcfox
As a side note, you could always use ORDER BY Id DESC, which is equally reliable (ID values are always created in ascending order).
SalesRedSalesRed

Thanks for the help.  Has been much appreciated as always!