List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 12 2007 7:39pm
Subject:Need help with a "natural sort order" for version numbers and release code names
View as plain text  
 
I'm trying to get some 'release/version numbers' to sort properly.

mysql> SELECT ReleaseID, Name FROM releases ORDER BY Name DESC;        
+-----------+---------------------+
| ReleaseID | Name                |
+-----------+---------------------+
|        18 | Unspecified         | 
|        20 | Next Patch          | 
|        58 | LOCset              | 
|        74 | Abashiri            | 
|        54 | 4.6.0 (Folsom)      | 
						  <-- 4.5.10 should be here
|        99 | 4.5.9               | 
|        98 | 4.5.6               | 
|        93 | 4.5.5 (Purdy)       | 
|        97 | 4.5.4               | 
|        96 | 4.5.3               | 
|        94 | 4.5.2               | 
|       100 | 4.5.10              |   <-- should be ^ there
|        91 | 4.5.1 Deferred      | 
|        78 | 4.5.1 (Leavenworth) | 
|        95 | 4.2.7.4             | 
|        92 | 4.2.7.3             | 
|        90 | 4.2.7.2             | 
|        87 | 4.2.7.1             | 
|        88 | 4.2.7.0             |  

I like this order, especially with the top four, 
except for that 4.5.10 should be higher up, 
just under 4.6.0, not under 4.5.2 as it is now.

So I tried the " + 0 " trick which makes things even worse 
(notice the 4.2.6.1 and 4.2.6.0 -- yipes!):

mysql> SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC;
+-----------+---------------------+
| ReleaseID | Name                |
+-----------+---------------------+
						 ....(18,20,58,74) are moved
:(
|        54 | 4.6.0 (Folsom)      | 
|        78 | 4.5.1 (Leavenworth) | 
|       100 | 4.5.10              | 
|        91 | 4.5.1 Deferred      | 
|        93 | 4.5.5 (Purdy)       | 
|        94 | 4.5.2               | 
|        96 | 4.5.3               | 
|        97 | 4.5.4               | 
|        98 | 4.5.6               | 
|        99 | 4.5.9               | 
|        82 | 4.2.6.1             |  <<  ?
|        76 | 4.2.2               | 
|        75 | 4.2.4               | 
|        72 | 4.2.1               | 
|        73 | 4.2.3               | 
|        67 | 4.2.6.0             |  <<  ?


I'm pretty sure this is going to involve some sort of splitting the version
from the release codeword via some string functions, and then operating on
that part.


D.Vin
http://daevid.com
---
eval() is my favorite templating engine.


Thread
Need help with a "natural sort order" for version numbers and release code namesDaevid Vincent12 Oct
  • Re: Need help with a "natural sort order" for version numbers andrelease code namesJay Pipes12 Oct
    • RE: Need help with a "natural sort order" for version numbers and release code namesDaevid Vincent12 Oct
  • Re: Need help with a "natural sort order" for version numbers and release code namesAdam Randall13 Oct
    • Re: Need help with a "natural sort order" for version numbers and release code namesAdam Randall13 Oct