Odd Sort Order by Alpha Varchar column
Hi. I have a wierd sort order problem with MySQL 5.6.7. My table:
CREATE TABLE `Server` (
`Sid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ServerName` varchar(32) DEFAULT NULL,
`DateAdded` datetime DEFAULT NULL,
`DateModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`Manufacturer` varchar(32) DEFAULT NULL,
`Model` varchar(64) DEFAULT NULL,
`Firmware` varchar(64) DEFAULT NULL,
`SType` enum('VM','LPAR','Blade','Rackmount','Appliance','Other','Unknown') DEFAULT 'Unknown',
`SerialNumber` varchar(64) DEFAULT NULL,
`Environment` enum('Production','Test','Development','DR','Retired','Other','Unsupported') DEFAULT NULL,
`Platform` enum('AIX','Linux','Solaris','Other') DEFAULT NULL,
`OS` enum('Redhat','SLES','CentOS','AIX','Solaris','Other','Ubuntu') DEFAULT NULL,
`OSVersion` varchar(32) DEFAULT NULL,
`MachineType` varchar(32) DEFAULT NULL,
`CPUModel` varchar(64) NOT NULL,
`CPUCores` decimal(5,2) DEFAULT NULL,
`CPUSpeed` varchar(32) DEFAULT NULL,
`Memory` varchar(32) DEFAULT NULL,
`DataCenter` enum('PDC','AOB','CAB','CH','Other','BHC','NorthBEF','Unknown') DEFAULT NULL,
`GridLocation` varchar(16) DEFAULT NULL,
`ManagementInterface` varchar(32) DEFAULT NULL,
`ManagementPort` varchar(16) DEFAULT NULL,
`BJCRoot` enum('Yes','No') DEFAULT 'No',
`Application` varchar(64) DEFAULT NULL,
`ApplicationContact` varchar(64) DEFAULT NULL,
`AppTeamHasRoot` enum('Yes','No') DEFAULT 'No',
`VendorSupported` enum('Yes','No') DEFAULT NULL,
`Comments` varchar(4095) DEFAULT NULL,
`Password` varchar(32) NOT NULL,
PRIMARY KEY (`Sid`),
UNIQUE KEY `SN` (`Sid`,`ServerName`)
) ENGINE=InnoDB AUTO_INCREMENT=1471 DEFAULT CHARSET=latin1
When I enter the sql command:
select ServerName,Platform,OS,OSVersion from Server order by Platform,OS,OSVersion;
I get (snippted of table):
| UDBCOTTRDB01 | Linux | Redhat | 6.6 |
| UPBCOSCM01 | Linux | Redhat | 6.6 |
| BJCESDEV03 | Linux | SLES | 10.1 |
| BJCESAPP02 | Linux | SLES | 10.2 |
| INTWEB | Linux | CentOS | 5.2 |
| MYBJCPROD12 | Linux | CentOS | 5.4 |
| CAB-EMGTEST-CACHE | Linux | Other | 0 |
| UPHCLA42 | Linux | Ubuntu | 10.04.4 LTS |
So, it seems to me that CentOS should be before Redhat. I tried collating latin1_bin, I tried utf8 and collating utf8, but it came out the same. I am fairly sure this is something simple I am missing, but I am just not seeing it.
Thanks for any replies.
Andrew Carlson
BJC Healthcare
Saint Louis, MO