ObjectHistory sort is not working correctly (UI as well as in SOQL)

Question

Hoping someone has in idea into what is going wrong with history object record entry and sorting. Here is the soql query i'm running: SELECT Id,Field,OldValue,NewValue, CreatedDate FROM "ObjectName__History" where ParentId = 'xxxe000000UWxxx' order by CreatedDate desc

1. First issue, if you see the created record which is supposed to be at the last when sorted 'desc' is at 3rd from bottom
2. 'Last Modified By' is an custom field, looking up an contact, now this field is populated on insert and update. If we look at the history, there are bunch of updates seen on the field, however there was only 2 update done on it (each update has 2 record entries one with contact ids and other with Name). The latest update is not seen on the record but seen on history(strange!!!)

All in all the query as well as the actual related list both are showing incorrect chronology. In case someone has a solution to get this sort correct kindly help, as it seems createdDate is not helping much.

Regards

Answer 1

When two dates are the same in the history field you cannot depend on the DESC for the order, you might want to either filter out WHEN FIELD = 'Created' or move it to the botto of the list using APEX.For your second issue: always when a lookup field is updated it logs twice to the history object (once for the id, and once for the name). You can fetch only the names instead of ids using WHERE DataType != 'EntityId'.