display all data in "hers" and "his" sorted by id : Sort Order « Select Clause « SQL / MySQL

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

display all data in "hers" and "his" sorted by id

       
mysql>
mysql> CREATE TABLE IF NOT EXISTS hers
    -> ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(20) );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO hers ( name ) VALUES ("Linda");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO hers ( name ) VALUES ("Donna");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO hers ( name ) VALUES ("Kay");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS his
    -> ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(20) );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO his ( name ) VALUES ("Michael");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO his ( name ) VALUES ("David");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO his ( name ) VALUES ("Andrew");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> # display all data in the "hers" table
mysql> SELECT * FROM hers;
+----+-------+
| id | name  |
+----+-------+
|  1 | Linda |
|  2 | Donna |
|  3 | Kay   |
+----+-------+
3 rows in set (0.00 sec)

mysql>
mysql> # display all data in the "his" table
mysql> SELECT * FROM his;
+----+---------+
| id | name    |
+----+---------+
|  1 | Michael |
|  2 | David   |
|  3 | Andrew  |
+----+---------+
3 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM hers
    -> UNION
    -> SELECT * FROM his
    -> ORDER BY id;
+----+---------+
| id | name    |
+----+---------+
|  1 | Linda   |
|  1 | Michael |
|  2 | Donna   |
|  2 | David   |
|  3 | Kay     |
|  3 | Andrew  |
+----+---------+
6 rows in set (0.00 sec)

mysql>
mysql> # display all data in "hers" and "his"
mysql> # sorted by name
mysql> SELECT * FROM hers
    -> UNION
    -> SELECT * FROM his
    -> ORDER BY name;
+----+---------+
| id | name    |
+----+---------+
|  3 | Andrew  |
|  2 | David   |
|  2 | Donna   |
|  3 | Kay     |
|  1 | Linda   |
|  1 | Michael |
+----+---------+
6 rows in set (0.00 sec)

mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS hers;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS his;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
    
  

Related examples in the same category