Natural Sort Order
I wrote about and came across this “problem” in my final year project and it’s something so simple yet hard to fix even with todays high level languages like SQL, c#.
The Basics
There are many ways to sort a list of items but the natural way would be numbers then letters. For example:
1
2
5
10
11
32
Alpha
Alpha1
Delta
Notice it orders the lowest value first in numbers then when it’s a string it lists them alphabetically.
Now consider this:
1
10
11
2
32
5
Alpha
Alpha1
Delta
Notice how 10, 11 appears before 2 and 32 appears before 5? This kind of order would occur if the numbers were being treated as text/string instead of integers. It compares the first character first and only compares subsequent letters only if the first character is the same. This is called lexicographical algorithm.
Applications
Whilst sorting may not be life or death in the desktop world it would be odd if you got an order from a professional company which orders it’s information in a certain fashion for example the stock exchange (Not that any financial services has this problem that I have seen). If a list of companies were ordered by their symbol or even the company name it will suffer this problem.
It isn’t very “human” like sorting. I have encountered this problem in MySQLbut Firebird database does not have this problem.
According to my lecturer (Aidan Delaney) there may be an option in the database to change the sort algorithm I haven’t seen it in MySQL.
