The expressions display state names in lexical order within each row : Sort Order « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. Sort Order

The expressions display state names in lexical order within each row

       
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.01 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>
mysql> SELECT YEAR(s2.statehood) AS year,
    -> IF(s1.name<s2.name,s1.name,s2.name) AS state1,
    -> IF(s1.name<s2.name,s2.name,s1.name) AS state2
    -> FROM states AS s1, states AS s2
    -> WHERE YEAR(s1.statehood) = YEAR(s2.statehood)
    -> AND s1.name != s2.name
    -> ORDER BY year, state1, state2;
+------+-------------+---------------+
| year | state1      | state2        |
+------+-------------+---------------+
| 1788 | Connecticut | Georgia       |
| 1788 | Connecticut | Georgia       |
| 1788 | Connecticut | Maryland      |
| 1788 | Connecticut | Maryland      |
| 1788 | Connecticut | Massachusetts |
| 1788 | Connecticut | Massachusetts |
| 1788 | Georgia     | Maryland      |
| 1788 | Georgia     | Maryland      |
| 1788 | Georgia     | Massachusetts |
| 1788 | Georgia     | Massachusetts |
| 1788 | Maryland    | Massachusetts |
| 1788 | Maryland    | Massachusetts |
| 1959 | Alaska      | Hawaii        |
| 1959 | Alaska      | Hawaii        |
+------+-------------+---------------+
14 rows in set (0.00 sec)

mysql>
mysql> drop table states;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
    
  

Related examples in the same category