I have to keep going back to ArcGIS to sort a dataset spatially using the PEANO method that's described here - http://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/how-sort-works.htm#ESRI_SECTION1_9C199CD3D68A41029943D4FF79500A73
I want to try to achieve the same using SQL - I have asked previously trying to do it using QGIS and haven't got a workable response.
For my data it's critical that the sort doesn't result in a multipart dataset unless two or more road segments with the same name are not connected. If they don't connect there's an issue with the segment.
The solution should work in the QGIS implementation of SQL that only allows the following - https://sqlite.org/lang.html
The data has roads that are segmented and sometimes the segments aren't sequential but join other segments on the same road. This causes issues when calculating the start/end/midpoints of the road once the data is joined by road name and so I need to sort it/dissolve it spatially.