Ordering numerical column in non-numerical order

Ordering numerical column in non-numerical order

TooManyTablesTooManyTables Posts: 23Questions: 7Answers: 1

Please forgive the odd title on this one - it's an odd requirement all around.

So, I've got someone with a bunch of numerical identifiers that represent various organisational units. They're hierarchical, like so (only the numbers are used, the names are just for your information):

387 - Parent Division 1
3871 - Sub Division
38711 - Area 1
387111 - A1 Team 1
387112 - A1 Team 2
38712 - Area 2
387121 - A2 Team 1
456 - Parent Division 2
4561 - Sub Division
45611 - Area 2

And so on. Some of the numbers go quite deep, 7 or 8 long. Not how I would have set up the identifiers, but that was decided long before I came on the scene, and they aren't going to change now.

Obviously, DataTables' inbuilt sorting displays the units out of order. All the Parent Divisions first, then all the sub divisions, then areas etc. Equally obviously, this isn't what the users want. Alternatives like padding the identifiers with 0s have been rejected. They must be in order, and they must display exactly as is. I could probably cobble together some kind of monstorous CTE that gives me a giant sort string, but as soon as they order on another column, that initial order is gone and can't be retrieved. Cue much wailing and gnashing of teeth.

Is there a simple (or even not-simple) way to order a bunch of numbers like I've laid out above so that it can be done on load, and renewed when the user clicks the Unit Code column after sorting on something else? It's kind of hard to describe the actual sorting process that has to happen - hopefully the example is clear enough.

This question has an accepted answers - jump to answer


  • colincolin Posts: 3,280Questions: 0Answers: 584
    Answer ✓

    Hi @TooManyTables (is there ever too many!? ;) )

    There's several custom sorting plugins, and this natural one may be just up your street.

    If that doesn't work, we're happy to take a look. As per the forum rules, if you could link to a running test case showing the issue we can offer some help. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.



  • kthorngrenkthorngren Posts: 4,551Questions: 19Answers: 996

    Maybe using orthogonal data would work. Your idea of padding the number with trailing zeros for the sort operation may work.


  • TooManyTablesTooManyTables Posts: 23Questions: 7Answers: 1
    edited November 2018

    @colin With DataTables, there are never too many tables. My username is how I was feeling when I discovered it. :tongue:

    The natural sort you linked worked perfectly. Once installed, a simple coulmnDef call had it purring like a kitten.

Sign In or Register to comment.