1

I have a list of records with name fields that all contain something like 'Name1', 'Name2, ..., 'Name20'. It's also not guaranteed that there will be a complete list of 1-20, i.e. it can be a list of 1-13 at times.

I'm looking to sort these records by that Name field where they'll be listed from 1-20, however I'm having difficulty achieving it in one SOQL query.

[SELECT Id, Name FROM MyObject WHERE Name LIKE 'Name%' ORDER BY Name ASC]

This query results in an order that looks like 'Name10', 'Name11', 'Name12', 'Name13', 'Name1', 'Name2', ... , 'Name9'.

I can do two SOQL statements to get single digit & double digit values, like so:

[SELECT Id, Name FROM MyObject WHERE Name LIKE 'Name_' ORDER BY Name ASC]
[SELECT Id, Name FROM MyObject WHERE Name LIKE 'Name__' ORDER BY Name ASC]

But this results in two lists that would then have to be concatenated together, which I was hoping to avoid.

Really I'm just hoping there's some secret sneaky way to accomplish this in one query. Any help with this would greatly be appreciated!

2 Answers 2

7

The ordering that looks like [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 3, 4, 5, 6, 7, 8, 9] is called lexicographic ordering. When you have a mix of alpha and numeric characters, that's the reasonable approach to take (and how SOQL and many other systems handle it).

If you want numerical ordering ([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, ...]), you'll need to do some additional work somewhere.

One approach you could take would be to create a formula field (returning a Decimal) to strip out the "Name" portion of the name, leaving you with just the numeric value. You could then ORDER BY <your formula field> to achieve the ordering you're looking for.

0

You're sorting lexicographically, so that means your data would have to be in the right order--that means renaming your data to Name01-Name20; notice the leading 0 for values less than 10. This would be the appropriate way to get the data into a single query.

You must log in to answer this question.

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