List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 12 2007 9:34pm
Subject:RE: Need help with a "natural sort order" for version numbers and release code names
View as plain text  
 

> -----Original Message-----
> From: Jay Pipes [mailto:jay@stripped] 
> Sent: Friday, October 12, 2007 2:07 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: Need help with a "natural sort order" for 
> version numbers and release code names
> 
> Daevid Vincent wrote:
> >  
> > 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.

> What about ORDER BY REPLACE(Name, '.', '') + 0 DESC?

Thanks for the tip Jay, but nope. It's all out of whack then.

mysql> SELECT ReleaseID, Name FROM releases ORDER BY REPLACE(Name, '.', '')
+ 0 DESC;
+-----------+---------------------+
| ReleaseID | Name                |
+-----------+---------------------+
|       100 | 4.5.10              | 
|        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             | 
|        86 | 4.2.6.3             | 
|        83 | 4.2.6.2             | 
|        82 | 4.2.6.1             | 
|        67 | 4.2.6.0             | 
|        85 | 4.2.5.4             | 
|        84 | 4.2.5.3             | 
|        80 | 4.2.5.2             | 
|        79 | 4.2.5.1             | 
|        54 | 4.6.0 (Folsom)      | 
|        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               | 
|        91 | 4.5.1 Deferred      | 
|        78 | 4.5.1 (Leavenworth) | 

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