Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

[SimonCrutchley]

I'm running an app built in WebApp Builder to display archaeological reports. I've built a query to allow searching by date, author etc as well as the map search. The query is supposed to sort first by date (formatted as a number it's only the year that matters) and then by report number (also formatted as a number). However, when running the query it sorts the output by date, but then seems to count the Number as text because it counts 1,2,3,4,5,6,10,100,101-109,11,110-119,12 etc. Has anyone come across such an issue before, and have you any idea how to rectify it?

I could reformat the field as text, adding leading zeros, but that seems counter intuitive, when I have a numeric field

Thanks

[DanPatterson]

You want what is called a "natural sort"

I can only demonstrate using python...

 a = ['1', '21', '3', 'r4', 'r41', '7', '71', 'r711', '10', 'r100']
b = sorted(a)
print("input - \n{}".format(a))
print("text sort - \n{}".format(b))
vals = natsort(a)
print("natural sort - \n{}".format(vals))
input - 
['1', '21', '3', 'r4', 'r41', '7', '71', 'r711', '10', 'r100']
text sort - 
['1', '10', '21', '3', '7', '71', 'r100', 'r4', 'r41', 'r711']
natural sort - 
['1', '3', '7', '10', '21', '71', 'r4', 'r41', 'r100', 'r711']

The process and code

import re
def natsort(lst):
    """natural sort"""
    import re
    convert = lambda text: int(text) if text.isdigit() else text
    a_key = lambda key: [convert(c) for c in re.split('([0-9]+)', key)]
    return sorted(lst, key=a_key)

[SimonCrutchley]

Hi Dan,

Thanks for that, but I still don't understand why it isn't just sorting 'naturally' anyway. The field is numeric, so surely it should sort 1,2,3,4,5,6,7,8,9,10,11,12 etc.

I'm afraid I don't understand the code etc.

Sorry

[DanPatterson]

It would sort in numeric order if it were numeric.  If it is sorted as you show, it is text and to get it to sort as a number, it has to be converted from text to number.  A number can look like a number, but you may not see what makes it appear as text.

The code sample is what could be used for field calculations in tables in ArcGIS Pro, for instance

[SimonCrutchley]

But it is numeric, at least according to the field data (see attached). That's what I don't understand.

[DanPatterson]

apparently the combination of date formatted as a number and a number doesn't yield a sortable number.

[SimonCrutchley]

In the query, the date is all the same in all the examples I've tried; it's the No field, which is just a number, that fails to sort.