Take the 2-minute tour ×

I'm having a little problem with trying to sort the contents of a table programs by the column prog_id which holds the id of each program in the following format:


1.0.1, 1.0.2, 1.0.3, ..., 1.0.10, 1.0.11, ..., 1.1.0, 1.1.1 etc

When I sort by prog_id i get

1.0.1, 1.0.10, 1.0.11, 1.0.2, 1.0.3 ...

which is correct as far as MySQL goes but not correct for the order in which the data should display. I tried using another column, orderby in which I could save an index and order by that but I would have to enter the values manually and there are a few thousand rows in my table which would take quite a long time to do.

Any tricks I could use to get my data to display in the "proper" order? BTW, I'm using PHP & MySQL.

share|improve this question

3 Answers 3

up vote 0 down vote accepted

Not optimal solution -

...ORDER BY substring_index(prog_id, '.', 1), substring_index(substring_index(prog_id, '.', 2), '.', -1), substring_index(prog_id, '.', -1)

Odd solution, but try it -

...ORDER BY INET_ATON(prog_id)
share|improve this answer
WOW! That is one hell of an inspiration! –  bikey77 Mar 29 '11 at 11:28

You could split them into their constituent parts like:

       LENGTH(SUBSTRING_INDEX(prog_id, '.', 1 -1)) + 1),
       '.', '') AS id1,
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 2 -1)) + 1),
       '.', '') AS id2,
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 3 -1)) + 1),
       '.', '') AS id3
FROM programs
ORDER BY CAST(id1 AS INT(4)), CAST(id2 AS INT(4)), CAST(id3 AS INT(4))

The best method would be to create the the extra fields like yoda2k says, but if you don't have that access then you could use the above.

You could encapsulate that into a function like:

  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Then do:

SELECT SPLIT_STR(prog_id, '.', 1) AS id1,
   SPLIT_STR(prog_id, '.', 2) AS id2,
   SPLIT_STR(prog_id, '.', 3) AS id3,
FROM programs
share|improve this answer

You could use 3 fields e.g. major_version, minor_version, build_number, make them integer fields and use mysqls buildin "ORDER BY major_version,minor_version,build_number" which will order the fields in the desired way.

share|improve this answer

Your Answer


