Supporting the Community
The Orlando IT community has done a great job of promoting the upcoming BarCamp. The word is out, and over 170 people have registered for the event already. Like any community, though, to be successful some financial backing is needed. For this reason, we at both MindComet and Statiksoft have pledged our support and have signed on as sponsors. We’re excited to have BarCamp come to our home town, and we’re confident everyone’s going to take something useful away from it. You can too if you go, but you’ll have to register—space is limited. I look forward to seeing you there!
BarCamp Orlando 2007
BarCamp is coming to Orlando! I’ve been interested in attending some technical conferences for a while now, so BarCamp is coming just in time. I’m looking forward to some interesting presentations and meeting cool locals in the tech industry. I’ll be representing two companies I’m a part of, MindComet and Statiksoft. Others from both local businesses will be there. Also, brother plans to woo with a presentation of I don’t know what.
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 | +---------+
-
About
Sean Murphy is a freelance web application developer currently living near Orlando, FL. Aside from building his own applications and contributing to open source projects, he works with clients to provide top-notch consulting & development expertise.
