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  |
+---------+