※追記
変換ツールを作りました。
https://yuyabu.github.io/OrderByWithCaseExpressionGenerator/
SQLのORDER BYでは昇順・降順で特定のカラム(単独or 組み合わせ)の並びを指定できるが、CASE式を組み合わせて、任意のソート順を指定出来ることを最近知った。
(SQL内だけでこういうことが実現できるのが衝撃的)
前提となる一次テーブル
WITH students as(
SELECT 1 AS sid, '高橋太郎' AS sname FROM DUAL
UNION ALL
SELECT 2 AS sid, '山田幸助' AS sname FROM DUAL
UNION ALL
SELECT 3 AS sid, '鈴木美恵子' AS sname FROM DUAL
UNION ALL
SELECT 4 AS sid, '織田信長' AS sname FROM DUAL
)
SELECT * FROM students ORDER BY sid
| SID | SNAME |
|---|---|
| 1 | 高橋太郎 |
| 2 | 山田幸助 |
| 3 | 鈴木美恵子 |
| 4 | 織田信長 |
例えば上記のようなテーブルの場合
sidが
- 1→2→3→4
- 4→3→2→1
の並びで並び替えることはorder by にascまたはdescを指定することで
実現できるのは常識だが、CASE式をORDER BYの中で使うことで
- 4→2→3→1
のような順番でソートすることができる。
CASE式で任意のソート順を指定する
SELECT * FROM students
ORDER BY
CASE sid WHEN 4 THEN 1 ELSE 2 END,
CASE sid WHEN 2 THEN 1 ELSE 2 END,
CASE sid WHEN 3 THEN 1 ELSE 2 END,
CASE sid WHEN 1 THEN 1 ELSE 2 END,
sid
結果
SID SNAME
4 織田信長
2 山田幸助
3 鈴木美恵子
1 高橋太郎
用途
- オリジナルのソート順を作る(S → M → L等)
- 特定の要素を先頭に表示する(国コードの列挙で日本を先頭に表示等)
- 過去のエビデンスなどの資料に記載されている、特に規則のない順序に合わせたソートを実現する
補足
- パフォーマンスがあまり良くないのでアプリケーションで使うSQLに採用するときは注意してください。
- アプリで使う場合はソート順を定義した別テーブルを作成し、結合後にソート順を定義した列でソートしてください