ORDER BY Autonumber is not working

Question

In SOQL we are using ORDER BY a auto number field in a parent object. But the order is not proper.

For Ascending order we are getting the following output

For Descending order we are getting the following output

Why we are getting like this ? How to solve this ?

Answer 1

Since auto numbers are actually text, you'll get 1,10,11,up to 19,2,20,21,3 etc.. the way to fix this is by putting leading 0's. You can do that by modifying the display format of your auto number to be something like this:

{00000000}

, this will add leading 0 and 1 will be 00000001 2 will be 00000002, 3 is 00000003 etc.. and that way you'll have the correct order

Answer 2

Auto number fields are stored in Salesforce database as string... that's why you are seeing this order. Few suggestions

• Create a formula number field that has numeric value of the text field and use it for sorting.
• Implement your auto increment number field.
• Use custom code to sort your list.

Answer 3

This happens because auto numbers are treated as text AFAIK. If you consider the ordering for a collection of text, what you are getting is correct. It compares the first digit, if that is same then the next digit and so on.

Answer 4

ORDER BY CreatedDate is working . So simple we can use this without any changes in code or object definition.