【SQL】ORDER BY で並び替える順序を指定したい

バックエンド

こんにちは ryohei515 です。

ORDER BY で項目を並び替えられることは皆さんご存知かと思いますが、この並び替えの順序を指定したいこともあるかと思います。

例えば、検索のテキストボックスで部分一致検索を実現したいとき、完全一致したものを最初に持ってきたいケースなどです。
今回だと大文字小文字を区別しないで検索させる要件がありました。

弊社では PostgreSQL を使っているので、PostgreSQL 固有の ILIKE を使います。
PostgreSQL: Documentation: 16: 9.7. Pattern Matching

SELECT
  name
FROM
  persons
WHERE
  name ILIKE '%john%'
ORDER BY
  name
;

このとき、例えば以下の 4 人が引っかかったとします。

name
John
Johnson
john
johnson

ORDER BY に name を指定しているのでこのように並びます。（A → B → ... → Z → a → b → ... → z の順）
ですが、このとき完全一致したもの（この例でいうと john）を先頭に持ってきたいとき、どのように書けばいいのでしょうか。

name
john
John
Johnson
johnson

結論

CASE を使うことで実現できます。
今回であれば ORDER BY で使ってあげるような形です。

SELECT
  name
FROM
  persons
WHERE
  name ILIKE '%john%'
ORDER BY
  CASE WHEN name = 'john' THEN 0
       ELSE 1
  END,
  name
;

これで、john と完全一致しているものを先頭に並べつつ、その他のものは name の昇順で並べることが実現できています。

CASEとは？

CASE は SQL 標準で使えるステートメントです。
条件に基づいて異なる値を返すことができます。

結論に記載した SQL では、john に合致したものは 0、それ以外を 1 を返すようにした CASE 式を ORDER BY の1要素目に持ってきています。
これにより、完全一致した 0 の行を先に並べることができています。

ちなみに今回は ORDER BY で CASE 式を使っていますが、SELECT や WHERE でも使えます。
例えば、年齢によってカテゴリ分けしたいときなど使い所です。

SELECT
  name,
  age,
  CASE
    WHEN age < 13 THEN 'Child'
    WHEN age BETWEEN 13 AND 19 THEN 'Teenager'
    WHEN age BETWEEN 20 AND 64 THEN 'Adult'
    ELSE 'Senior'
  END as age_category
FROM persons
ORDER BY name;

出力は以下のようになります。
SQL のみでカテゴリ分けができるので、データ調査などでもよく使えて便利です。

name age age_category
Alice 8 Child
Bob 50 Adult
John 30 Adult
Johnson 77 Senior
john 28 Adult
johnson 19 Teenager

おわりに

SELECT や WHERE 内では、これまでもよく CASE を使うことはあったのですが、ORDER BY で使えることが新鮮だったので記事にしてみました。
CASE は使いこなせると非常に便利なので、あまり使ったことがなかったという方は頭の片隅にでも置いてもらえるといいなと思います！

インゲージではエンジニアを募集中です！
詳細は以下のリンクよりご確認ください！

ingage.co.jp