Mega Code Archive

 
Categories / MySQL / Select Clause
 

To make the lexical ordering correspond to the numeric ordering

mysql> mysql> CREATE TABLE states     -> (     ->  name            VARCHAR(30) NOT NULL,   # state name     ->  abbrev          CHAR(2) NOT NULL,               # 2-char abbreviation     ->  statehood       DATE,                                   # date of entry into the Union     ->  pop                     BIGINT,                                 # population as of 4/1990     ->  PRIMARY KEY (abbrev)     -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into states(name, abbrev, statehood, pop)values     -> ("Alabama","AL","1819-12-14",4040587),     -> ("Alaska","AK","1959-01-03",550043),     -> ("Arizona","AZ","1912-02-14",3665228),     -> ("Arkansas","AR","1836-6-15",2350725),     -> ("California","CA","1850-9-9",29760021),     -> ("Colorado","CO","1876-8-1",3294394),     -> ("Connecticut","CT","1788-1-9",3287116),     -> ("Delaware","DE","1787-12-7",666168),     -> ("Florida","FL","1845-3-3",12937926),     -> ("Georgia","GA","1788-1-2",6478216),     -> ("Hawaii","HI","1959-08-21",1108229),     -> ("Idaho","ID","1890-7-3",1006749),     -> ("Illinois","IL","1818-12-3",11430602),     -> ("Indiana","IN","1816-12-11",5544159),     -> ("Iowa","IA","1846-12-28",2776755),     -> ("Kansas","KS","1861-1-29",2477574),     -> ("Kentucky","KY","1792-6-1",3685296),     -> ("Louisiana","LA","1812-4-30",4219973),     -> ("Maine","ME","1820-3-15",1227928),     -> ("Maryland","MD","1788-4-28",4781468),     -> ("Massachusetts","MA","1788-2-6",6016425),     -> ("Michigan","MI","1837-1-26",9295297),     -> ("Minnesota","MN","1858-5-11",4375099); Query OK, 23 rows affected (0.00 sec) Records: 23  Duplicates: 0  Warnings: 0 mysql> mysql> SELECT CONCAT(LPAD(pop,8,' '),name) FROM states; +------------------------------+ | CONCAT(LPAD(pop,8,' '),name) | +------------------------------+ |  4040587Alabama              | |   550043Alaska               | |  3665228Arizona              | |  2350725Arkansas             | | 29760021California           | |  3294394Colorado             | |  3287116Connecticut          | |   666168Delaware             | | 12937926Florida              | |  6478216Georgia              | |  1108229Hawaii               | |  1006749Idaho                | | 11430602Illinois             | |  5544159Indiana              | |  2776755Iowa                 | |  2477574Kansas               | |  3685296Kentucky             | |  4219973Louisiana            | |  1227928Maine                | |  4781468Maryland             | |  6016425Massachusetts        | |  9295297Michigan             | |  4375099Minnesota            | +------------------------------+ 23 rows in set (0.00 sec) mysql> mysql> drop table states; Query OK, 0 rows affected (0.00 sec)