It seems to be a quirk of ordering SOQL queries by CreatedDate that, if the records were created in the same second, the order returned by the SOQL query doesn't reflect the order of execution, as demonstrated here.
I'm running into this problem when trying to query the approval process history for a custom object, in that the submission step is appearing before the next pending steps.
List<ProcessInstance> allApprovalProcesses = new List<ProcessInstance>(
[SELECT Id, TargetObjectId, Status, CompletedDate, CreatedDate, ProcessDefinitionId,
(SELECT Id, StepStatus, Originalactor.Name, Actor.Name, CreatedDate
FROM StepsAndWorkitems
ORDER BY Id, CreatedDate DESC),
(SELECT Id, Actor.Name
FROM Workitems),
(SELECT Id, StepStatus, Actor.Name, Originalactor.Name, CreatedDate
FROM Steps)
FROM ProcessInstance
WHERE TargetObjectId IN :proposalIDs
ORDER BY TargetObjectId, CreatedDate DESC]);
Even if I order by both CreatedDate and Id descending, as suggested in the above link, the 'Started' (i.e. 'Submitted') step appears before the 'Pending' steps.
05:45:54.0 (38218906)|USER_DEBUG|[359]|DEBUG|***
(ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04h11000001OSmAAAW, StepStatus=Started, OriginalActorId=00511000004iKuJAAU, ActorId=00511000004iKuJAAU, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up4AAE, StepStatus=Pending, OriginalActorId=00GG0000005hz2FMAQ, ActorId=00GG0000005hz2FMAQ, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up5AAE, StepStatus=Pending, OriginalActorId=00GG0000005hz2EMAQ, ActorId=00GG0000005hz2EMAQ, CreatedDate=2016-11-02 10:58:11},
ProcessInstanceHistory:{ProcessInstanceId=04g11000001N0k9AAC, Id=04i110000012Up6AAE, StepStatus=Pending, OriginalActorId=00511000004iKuJAAU, ActorId=00511000004iKuJAAU, CreatedDate=2016-11-02 10:58:11})
Anyone have any ideas how I can force the SOQL ordering as per the actual approval history below?
Thanks!
Best Answer
I've been able to make it work with the following query:
The catch is to order first by the Created Date and THEN by the Id.