MySQL の ORDER BY で NULL を先にもってきて NULL 以外は降順にする

MySQL

こんなんできたのね・・・。

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 おすすめ書籍

MySQL おすすめ書籍#1 MySQL おすすめ書籍#2

MySQL