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?

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

2 Answers 2

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
7
  • 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, 2019 at 10:26
  • Yes, question is valid. I just said that the Order By is behaving correctly since the data type is text. Aug 6, 2019 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, 2019 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' Aug 6, 2019 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, 2019 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .