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

1224
6
12-08-2020 03:37 AM
Labels (1)
SimonCrutchley
Occasional Contributor III

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

Tags (2)
0 Kudos
6 Replies
DanPatterson
MVP Esteemed Contributor

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)

... sort of retired...
0 Kudos
SimonCrutchley
Occasional Contributor III

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

0 Kudos
DanPatterson
MVP Esteemed Contributor

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


... sort of retired...
0 Kudos
SimonCrutchley
Occasional Contributor III

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

0 Kudos
DanPatterson
MVP Esteemed Contributor

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

 


... sort of retired...
0 Kudos
SimonCrutchley
Occasional Contributor III

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.

0 Kudos