> -----Original Message----- > From: Jay Pipes [mailto:[EMAIL PROTECTED] > Sent: Friday, October 12, 2007 2:07 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > 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) | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
