0

I'm facing a problem with the SOQL ORDER BY. I'm quering on a custom object with this query

SELECT completeTreeSequence__c 
FROM CPQ_Line__c 
WHERE CPQ_Header_RU__r.Id = 'a043E000005PfD7QAK' 
ORDER BY completeTreeSequence__c ASC 
LIMIT 10

where completeTreeSequence__c is a text formula field

this is the result:

enter image description here

Results are wrong beacuse the item 5--> "1.1.2.1.10" should be after the item 6 -->"1.1.2.1.1.1".

I think the "10" is interpreted as "1" and this is why it comes with the wrong order.

Does anyone has faced this error and solved it?

  • For reference, the reason the sort order appears this way is because of lexicographical ordering. – Derek F Aug 8 at 13:52
2

This ordering is actually correct because the field type is text. It will be same whichever language (database/server/javascript) you do a sort. Since you have multiple dots, you cannot even set the data type as number. The only option is to get the records and write a custom logic to sort them correctly.

adding based on comments:

Adding 0 still is failing:

var nums = ["01.02.01", "01.02.08", "01.02.011"];
nums.sort(); // 01.02.01, 01.02.011, 01.02.08
  • Problem is that in UTF table dot comes before zero or one (f.e. utf8-chartable.de/unicode-utf8-table.pl), so basically questions is valid. I am able to reproduce it – kurunve Aug 6 at 10:26
  • Yes, question is valid. I just said that the Order By is behaving correctly since the data type is text. – salesforce-sas Aug 6 at 10:28
  • 1
    i tried to create folders on windows with the same name of that field value and the order is correct right there. Also i've solved the issue padding left to 2 digits with '0' – gmemo Aug 6 at 10:33
  • Windows will have custom logic to deal with file names. I didnt understand what you mean by padding left to 2 digits with '0' – salesforce-sas Aug 6 at 10:37
  • I've changed the formula that creates that field. Before was 1.10 comes before 1.1.1 --- Now is 01.10 and 01.01.01 and the order is correct as 01.01.01 comes before 01.10 – gmemo Aug 6 at 10:40
0

I've resolved using a workaround padding every element of the string to 2 digits adding '0' to the left.

now 1.1.1 is sorted before 1.10 as i was expecting

enter image description here

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.