MySQL の ORDER BY で NULL を先にもってきて NULL 以外は降順にする
こんなんできたのね・・・。
ORDER BY created_at IS NULL ASC, created_at ASC
たとえばこんなデータで試すと…
CREATE TABLE test ( id INT(1), created_at DATETIME );
INSERT INTO test ( id, created_at ) VALUES ( 1, '2010-05-17 15:29:00' ), ( 2, '2011-05-17 15:29:00' ), ( 3, '2012-05-17 15:29:00' ), ( 4, NULL ), ( 5, NULL);
これを created_at 昇順で取得すると NULL が先にきてしまう。
SELECT * FROM test ORDER BY created_at ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 4 | NULL |
+----+---------------------+
| 5 | NULL |
+----+---------------------+
| 1 | 2010-05-17 15:29:00 |
+----+---------------------+
| 2 | 2011-05-17 15:29:00 |
+----+---------------------+
| 3 | 2012-05-17 15:29:00 |
+----+---------------------+
逆に日付昇順ソートで NULL を最後にもってくるには…
SELECT * FROM test ORDER BY created_at IS NULL ASC, created_at ASC;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2010-05-17 15:29:00 |
+----+---------------------+
| 2 | 2011-05-17 15:29:00 |
+----+---------------------+
| 3 | 2012-05-17 15:29:00 |
+----+---------------------+
| 4 | NULL |
+----+---------------------+
| 5 | NULL |
+----+---------------------+
ほほーう。
参考
- https://dev.mysql.com/doc/refman/5.6/ja/working-with-null.html
MySQL おすすめ書籍
