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)