created_at = updated_at のレコードを無視してソートしたい
やりたいこと
updated_at でソートしたい。ただし、created_at と updated_at が同じ値のレコードはいちばん古い日時としてあつかいたい。
ソート前
+------------------------------------------------+
| id | created_at | updated_at |
+------------------------------------------------+
| 1 | 2021-04-02 02:34:47 | 2021-04-02 02:34:47 | <- created_at = updated_at
+------------------------------------------------+
| 2 | 2021-04-02 02:34:47 | 2021-04-02 12:34:47 |
+------------------------------------------------+
| 3 | 2021-04-02 02:34:47 | 2021-04-02 22:34:47 |
+------------------------------------------------+
| 4 | 2021-04-02 02:34:47 | 2021-04-02 02:34:47 | <- created_at = updated_at
+------------------------------------------------+ソート後（ASC）
+------------------------------------------------+
| id | created_at | updated_at |
+------------------------------------------------+
| 1 | 2021-04-02 02:34:47 | 2021-04-02 02:34:47 | <- created_at = updated_at
+------------------------------------------------+
| 4 | 2021-04-02 02:34:47 | 2021-04-02 02:34:47 | <- created_at = updated_at
+------------------------------------------------+
| 2 | 2021-04-02 02:34:47 | 2021-04-02 12:34:47 |
+------------------------------------------------+
| 3 | 2021-04-02 02:34:47 | 2021-04-02 22:34:47 |
+------------------------------------------------+ソート後（DESC）
+------------------------------------------------+
| id | created_at | updated_at |
+------------------------------------------------+
| 3 | 2021-04-02 02:34:47 | 2021-04-02 22:34:47 |
+------------------------------------------------+
| 2 | 2021-04-02 02:34:47 | 2021-04-02 12:34:47 |
+------------------------------------------------+
| 1 | 2021-04-02 02:34:47 | 2021-04-02 02:34:47 | <- created_at = updated_at
+------------------------------------------------+
| 4 | 2021-04-02 02:34:47 | 2021-04-02 02:34:47 | <- created_at = updated_at
+------------------------------------------------+どうやるの？
最後に updated_at をつけることを忘れずに。
-- ASC の場合
SELECT * FROM table ORDER BY CASE WHEN created_at = updated_at THEN 1 ELSE 2 END, updated_at ASC
-- DESC の場合
SELECT * FROM table ORDER BY WHEN CASE created_at = updated_at THEN 2 ELSE 1 END, updated_at DESC