mysql sorting of version numbers

by

Try abusing the INET_ATON function to do the sorting like so:

SELECT version_number FROM table ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version_number,'.0.0.0'),'.',4))

This trick was originally posted on the mysql mailing list, so many thanks to the original poster, Michael Stassen!

Here’s what he had to say:

If each part is no larger than 255, you can leverage INET_ATON() to do
what you want (up to the 4th part). The trick is making each of these
look like an IP first by using CONCAT to add ‘0.0.0’ to make sure every
row has at least 4 parts, then SUBSTRING_INDEX to pull out just the
first 4 parts.

Now, I must point out that because we are sorting on a function of the
column, rather than on the column itself, we cannot use an index on the
column to help with the sort. In other words, the sorting will be
relatively slow.

In the latter case, he recommends a solution similar to the one posted by @spanky (separate columns).

More Related Contents:

  1. How to find similar results and sort by similarity?
  2. Mysql: Select rows from a table that are not in another
  3. Sorting varchar field numerically in MySQL
  4. Mysql order by specific ID values
  5. MySQL: Sort GROUP_CONCAT values
  6. ORDER BY date and time BEFORE GROUP BY name in mysql
  7. updating columns with a sequence number mysql
  8. is php sort better than mysql “order by”?
  9. mysql custom sort
  10. ORDER BY alphabet first then follow by number
  11. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)
  12. Out of memory Microsoft VBScript runtime error 800a0007
  13. MySQL how to fill missing dates in range?
  14. Left Outer Join doesn’t return all rows from my left table?
  15. How can i optimize MySQL’s ORDER BY RAND() function?
  16. ERROR 1396 (HY000): Operation CREATE USER failed for ‘jack’@’localhost’
  17. How to set a maximum execution time for a mysql query?
  18. SQL keys, MUL vs PRI vs UNI
  19. How to copy a row and insert in same table with a autoincrement field in MySQL?
  20. How to move columns in a MySQL table?
  21. access denied for load data infile in MySQL
  22. Where to change the value of lower_case_table_names=2 in XAMPP on Windows?
  23. What is the best MySQL collation for German language
  24. quick selection of a random row from a large table in mysql
  25. How can we find gaps in sequential numbering in MySQL?
  26. Optimization of MySQL search using “like” and wildcards
  27. Rank() over Partition by in mysql
  28. MySQL: set field default value to other column
  29. CREATE command denied to user？
  30. What is the MAX number if I store int(255) in MySQL?

Leave a Comment