Natural Order Numerical Sorting
I came across an interesting problem today in the CodeIgniter forums. Suppose you have a dataset like this:
1
1.2
1.2.1
1.2.3
1.2.4
2
5
5
5.1
5.7
11
11.1.2
Obviously, the example cited above is sorted, and sorted correctly. Now lets say these values exist in a random order in a database and we want to retrieve them in the same order as above–sorted ascending. The simple SELECT version FROM test ORDER BY version ASC doesn’t quite do the job.
+---------+ | version | +---------+ | 1 | | 1.2 | | 1.2.1 | | 1.2.3 | | 1.2.4 | | 11 | | 11.1.2 | | 2 | | 5 | | 5 | | 5.1 | | 5.7 | +---------+
This is because our data is stored as a string, and the database will sort it as such lexicographically. The thinking human, however, wants it sorted numerically. But we cant, it’s a string. “So convert it to a decimal”, you say. Well, that partly works, except, we loose everything after the second period. For example, SELECT version, (version + 0) FROM test ORDER BY (version + 0) ASC will implicitly cast our field to a decimal. But the results will look something like this:
+---------+---------------+ | version | (version + 0) | +---------+---------------+ | 1 | 1 | | 1.2.1 | 1.2 | | 1.2 | 1.2 | | 1.2.4 | 1.2 | | 1.2.3 | 1.2 | | 2 | 2 | | 5 | 5 | | 5 | 5 | | 5.1 | 5.1 | | 5.7 | 5.7 | | 11 | 11 | | 11.1.2 | 11.1 | +---------+---------------+
The second column is there to show what we’re actually sorting by. As you can see we’re close, but not there. The secret is to sort both numerically AND lexicographically (in that order). The new SQL query will be SELECT version FROM test ORDER BY (version + 0), version ASC. And the results are just what we wanted:
+---------+ | version | +---------+ | 1 | | 1.2 | | 1.2.1 | | 1.2.3 | | 1.2.4 | | 2 | | 5 | | 5 | | 5.1 | | 5.7 | | 11 | | 11.1.2 | +---------+
New Domains
As a few people know, I have wanted to own seanmurphy.com for some time. The only thing preventing me from buying it was the $3,500 price tag—it just didn’t seem reasonable. Granted, Sean Murphy is an awesome name. Further, to have your name as a domain is to be desired. But three and a half large?!
In the past I tried to come up with a suitable (and less expensive) alternative, but failed. Nothing could come close to seanmurphy.com. Well, let me take that back. There was one seanmurphy.ie that I would have been happy with, which I bought—and then was rejected registration. Apparently I’m required to have a “real and substantial connection with Ireland” in order to register an .ie domain. So I waited. And waited. Seanmurphy.com still cost $3,500. I still don’t have $3,500 to guiltlessly spend on a domain.
Alas!, I found an alternative I can live with: iamseanmurphy.com (and .net, and .org!). I have no idea why I didn’t think of it before. Maybe I did, but was dissatisfied with it. Maybe I didn’t, but no matter, that’s not the point. What is of note is that I have a fresh new domain with my name in it!.
-
-
About
Sean Murphy is a software developer and lifestyle entrepreneur currently living near Orlando, FL. He loves working with smart people, writing cutting edge software, sharing knowledge, and most of all, steak.
