mysqlで複数のorder by指定
構文 is
SELECT *
FROM tables
ORDER BY name1, name2
;
知っている人からすると当たり前で何を今更って感じかもしれないのですが、
name2でソートしてから、name1でソートされるみたいでした。
動作確認のログ
vagrant@homestead:~$ mysql -V
mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper
mysql> create database sample;
Query OK, 1 row affected (0.00 sec)
mysql> use sample;
Database changed
mysql> create table users (id int, name1 varchar(10), name2 varchar(10));
Query OK, 0 rows affected (0.04 sec)
insert into users values
(1, 'A', '1')
,(2, 'B', '1')
,(3, 'B', '1')
,(4, 'C', '1')
,(5, 'C', '2')
,(6, 'D', '1')
;
mysql> select *
-> from users
-> order by id;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | B | 1 |
| 4 | C | 1 |
| 5 | C | 2 |
| 6 | D | 1 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from users order by name1, name2;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | B | 1 |
| 4 | C | 1 |
| 5 | C | 2 |
| 6 | D | 1 |
+------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from users order by name2, name1;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | B | 1 |
| 4 | C | 1 |
| 6 | D | 1 |
| 5 | C | 2 |
+------+-------+-------+
6 rows in set (0.00 sec)
その他
昇順、降順をいじる場合
name1順にならべたいけど、name2は降順でいてほしい。
mysql> SELECT * FROM users ORDER BY name1 ASC, name2 DESC;
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | B | 1 |
| 5 | C | 2 |
| 4 | C | 1 |
| 6 | D | 1 |
+------+-------+-------+
6 rows in set (0.00 sec)