なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

ORDER BYで、単純な昇順降順「以外」で並べる!

いやー、知らないって怖いね。
なんだこのキモいSQLは、って思ってしまったけど、調べているウチに、これちゃんとSQL構文に則ってる!こちらが間違ってた!って事がわかっていきました。


あえて、知らなかった所から勢いで書いていたのを、そのままにしてみました。

キモいSQLコードを偶然見つけた

SQLにおけるORDER BYって、その後にカラム(およびそのエイリアス)を並べてソート順として使用するわけですが、MySQL案件のお仕事の中で偶然こんなものを見つけて、絵に描いたような二度見リアクションしました。

SELECT * FROM tbl ORDER BY id = 23; -- (1)

SELECT * FROM tbl ORDER BY FIELD( id, 23, 234, 543, 23 ); -- (2)

こうした、「ORDER BYに、あたかもWHERE句で絞り込む条件指定のような使い方」について、MySQL :: MySQL 5.6 リファレンスマニュアルの中から見つけ出すことができませんでした。



(2)の構文については、公式ドキュメントのコメント欄から見つけ出したものです。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9 SELECT 構文



ORDER BYの中でCASE句を切ってソート指定列を変えるとかは、あくまで列指定の切り替えなので理解の範囲内でした。


特殊なところでは、ランダムにレコード取得をする際にMySQLではORDER BY RAND()とか使うのは知っていますが、こういった、ソートオーダーの指定で直値を指定する形のものは初めて見ました。


というわけで、少し調べてみました。

いつものように、試してみる。

例によってVitualBox上のLinuxMySQL 5.6(5.6.31)で試しています。

まずは準備
mysql> CREATE DATABASE test_orderby DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_orderby;
Database changed
mysql> CREATE TABLE `test_tbl` (
    ->   `col_id` int NOT NULL PRIMARY KEY,
    ->   `col_char1` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.16 sec)

mysql> show create table test_tbl\G
*************************** 1. row ***************************
       Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
  `col_id` int(11) NOT NULL,
  `col_char1` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> INSERT INTO test_tbl (col_id,col_char1) values 
    -> (1,'a'),
    -> (2,'b'),
    -> (3,'c'),
    -> (4,'d'),
    -> (5,'e'),
    -> (6,'f'),
    -> (7,'g'),
    -> (8,'h'),
    -> (9,'i'),
    -> (10,'');
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT col_id,col_char1 FROM test_tbl;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|      1 | a         |
|      2 | b         |
|      3 | c         |
|      4 | d         |
|      5 | e         |
|      6 | f         |
|      7 | g         |
|      8 | h         |
|      9 | i         |
|     10 |           |
+--------+-----------+
10 rows in set (0.01 sec)
実験
mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY col_id DESC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|     10 |           |
|      9 | i         |
|      8 | h         |
|      7 | g         |
|      6 | f         |
|      5 | e         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|      1 | a         |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY col_id ASC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|      1 | a         |
|      2 | b         |
|      3 | c         |
|      4 | d         |
|      5 | e         |
|      6 | f         |
|      7 | g         |
|      8 | h         |
|      9 | i         |
|     10 |           |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY col_id = 5 DESC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|      5 | e         |
|      1 | a         |
|      9 | i         |
|      8 | h         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|     10 |           |
+--------+-----------+
10 rows in set (0.04 sec)

mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY col_id = 5 ASC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|      1 | a         |
|      9 | i         |
|      8 | h         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|     10 |           |
|      5 | e         |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY FIELD(col_id,1,5,8) DESC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|      8 | h         |
|      5 | e         |
|      1 | a         |
|      9 | i         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|     10 |           |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY FIELD(col_id,1,5,8) ASC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|     10 |           |
|      9 | i         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|      1 | a         |
|      5 | e         |
|      8 | h         |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY FIELD(col_id,1,8,5) DESC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|      5 | e         |
|      8 | h         |
|      1 | a         |
|      9 | i         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|     10 |           |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1 FROM test_tbl
    -> ORDER BY FIELD(col_id,1,8,5) ASC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|     10 |           |
|      9 | i         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|      1 | a         |
|      8 | h         |
|      5 | e         |
+--------+-----------+
10 rows in set (0.00 sec)

こんな感じ。この時点での感想は「わけがわからないよ。。。」。


救世主yoku0825先生現る。(こっちで勝手にぐぐったんだけど)

日々の覚書: WHERE .. IN (..)のリストの順番でソートするORDER BY FIELDの仕組み




なんと、「ORDER BY FIELD」という構文ではなく、「FIELD関数の結果でORDER BY」していると。


FIELD関数は、調べた限りMySQLにしか存在しない関数で、「第一引数が、第二引数以降に列挙したなかの何番目に現れるかを返す」関数です。
SQLという、行による集合演算メインの処理系において、なんでこんな関数が存在しているんだ?という印象がありますが、そこは後々その有難味がわかってくるのでしょう。


yoku0825さんのSQL文の書き換え例で、やっと意味が分かりました。
(EXPLAIN EXTENDED ~+SHOW WARNINGSでSQL解析・書き換え結果がみえるわけですが、残念ながら?書き換えは行われず元のSQL文のままでした。)

mysql> SELECT col_id,col_char1,
    -> FIELD(col_id,1,5,8) AS sort_order
    -> FROM test_tbl
    -> ORDER BY sort_order DESC;
+--------+-----------+------------+
| col_id | col_char1 | sort_order |
+--------+-----------+------------+
|      8 | h         |          3 |
|      5 | e         |          2 |
|      1 | a         |          1 |
|      9 | i         |          0 |
|      7 | g         |          0 |
|      6 | f         |          0 |
|      4 | d         |          0 |
|      3 | c         |          0 |
|      2 | b         |          0 |
|     10 |           |          0 |
+--------+-----------+------------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1,
    -> FIELD(col_id,1,5,8) AS sort_order
    -> FROM test_tbl
    -> ORDER BY sort_order ASC;
+--------+-----------+------------+
| col_id | col_char1 | sort_order |
+--------+-----------+------------+
|     10 |           |          0 |
|      9 | i         |          0 |
|      7 | g         |          0 |
|      6 | f         |          0 |
|      4 | d         |          0 |
|      3 | c         |          0 |
|      2 | b         |          0 |
|      1 | a         |          1 |
|      5 | e         |          2 |
|      8 | h         |          3 |
+--------+-----------+------------+
10 rows in set (0.00 sec)


このあたりで、最初「なにこれキモい」って言ってた自分を思い出して、顔が真っ赤になるわけです。


もう一つのほう、「ORDER BY 列名=直値」

yoku0825先生の「ORDER BY FIELD」に関する情報に倣うと、この直値指定もまた、「列名=直値」という「評価式の結果」でソートをしているのであろうと推測されます。


つまり、

SELECT col_id,col_char1
FROM test_tbl
ORDER BY col_id = 5 DESC;

というSQL

SELECT col_id,col_char1,
col_id = 5 as expr
FROM test_tbl
ORDER BY expr DESC;

と書き換えることができます。(この書き換え後のSQLもまだキモいのですが)

mysql> SELECT col_id,col_char1,
    -> col_id = 5 as expr
    -> FROM test_tbl
    -> ORDER BY expr DESC;
+--------+-----------+------+
| col_id | col_char1 | expr |
+--------+-----------+------+
|      5 | e         |    1 |
|      1 | a         |    0 |
|      9 | i         |    0 |
|      8 | h         |    0 |
|      7 | g         |    0 |
|      6 | f         |    0 |
|      4 | d         |    0 |
|      3 | c         |    0 |
|      2 | b         |    0 |
|     10 |           |    0 |
+--------+-----------+------+
10 rows in set (0.00 sec)

このとおり、「col_id = 5」という評価式の真(1)偽(0)でソートとなり、偽の集団に中においては、その表示順は保証されません。


「=」以外の色々な演算子でも、式として成立していれば、同じことができます。


つまり、リファレンスの構文説明にあるとおり、

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
(後略)
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.9 SELECT 構文

の「expr」に当てはまればなんでもあり、と。

MySQL方言を超えて

まず、FIELD関数がMySQL固有のものです。世にあるマルチRDBMSOracle/SQL Server/Access/PostgreSQL/MySQL/ANSI)なSQLリファレンス本を幾つか漁ってみましたが、同等の機能を持つ関数を実装している他のRDBMSは見つかりませんでした。
なので、これを多くのDBMSで通用しそうなSQLに置き換えるとすると、「CASE」がわかりやすいでしょう。

mysql> SELECT col_id,col_char1
    -> FROM test_tbl
    -> ORDER BY FIELD(col_id,1,8,5) ASC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|     10 |           |
|      9 | i         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|      1 | a         |
|      8 | h         |
|      5 | e         |
+--------+-----------+
10 rows in set (0.13 sec)

mysql> SELECT col_id,col_char1
    -> FROM test_tbl
    -> ORDER BY CASE col_id WHEN 1 THEN 1 WHEN 8 THEN 2 WHEN 5 THEN 3 ELSE 0 END ASC;
+--------+-----------+
| col_id | col_char1 |
+--------+-----------+
|     10 |           |
|      9 | i         |
|      7 | g         |
|      6 | f         |
|      4 | d         |
|      3 | c         |
|      2 | b         |
|      1 | a         |
|      8 | h         |
|      5 | e         |
+--------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT col_id,col_char1,
    -> CASE col_id WHEN 1 THEN 1 WHEN 8 THEN 2 WHEN 5 THEN 3 ELSE 0 END AS sort_order
    -> FROM test_tbl
    -> ORDER BY sort_order ASC;
+--------+-----------+------------+
| col_id | col_char1 | sort_order |
+--------+-----------+------------+
|     10 |           |          0 |
|      9 | i         |          0 |
|      7 | g         |          0 |
|      6 | f         |          0 |
|      4 | d         |          0 |
|      3 | c         |          0 |
|      2 | b         |          0 |
|      1 | a         |          1 |
|      8 | h         |          2 |
|      5 | e         |          3 |
+--------+-----------+------------+
10 rows in set (0.00 sec)


このように、CASEで置き換えができました。
これをOracleにぶん投げてみます。(テーブル準備は省略)

(Oracle12cR1です)

SQL> SELECT col_id,col_char1 FROM test_tbl ORDER BY CASE col_id WHEN 1 THEN 1 WHEN 8 THEN 2 WHEN 5 THEN 3 ELSE 0 END ASC;

    COL_ID COL_CHAR1
---------- ---------------
	10
	 7 g
	 2 b
	 3 c
	 4 d
	 9 i
	 6 f
	 1 a
	 8 h
	 5 e

10行が選択されました。

SQL> SELECT col_id,col_char1,CASE col_id WHEN 1 THEN 1 WHEN 8 THEN 2 WHEN 5 THEN 3 ELSE 0 END AS sort_order FROM test_tbl ORDER BY sort_order ASC;

    COL_ID COL_CHAR1	   SORT_ORDER
---------- --------------- ----------
	10			    0
	 7 g			    0
	 2 b			    0
	 3 c			    0
	 4 d			    0
	 9 i			    0
	 6 f			    0
	 1 a			    1
	 8 h			    2
	 5 e			    3

10行が選択されました。

このとおり、ORDER BYの中でのCASE、および、SELECT内でのCASE処理結果のエイリアス、双方とも同じように動きました。やったね!


「列名=直値」の方ですが、Oracleでそのままいけるかなーと思いましたが、残念ながら構文エラーです。
ORDER BYにおいても、SELECT側においてエイリアス指定の形にしても、構文エラーです。


なので、これもCASEに置き換え、WHEN THENを1つだけ、あとはELSEに流すようにしましょう。

追記:postgresもやってみた

PostgreSQL 9.5.3です)

postgres=# SELECT col_id,col_char1 FROM test_tbl ORDER BY CASE col_id WHEN 1 THEN 1 WHEN 8 THEN 2 WHEN 5 THEN 3 ELSE 0 END ASC;
 col_id | col_char1 
--------+-----------
     10 | 
      2 | b
      3 | c
      4 | d
      6 | f
      7 | g
      9 | i
      1 | a
      8 | h
      5 | e
(10 行)

postgres=# SELECT col_id,col_char1,CASE col_id WHEN 1 THEN 1 WHEN 8 THEN 2 WHEN 5 THEN 3 ELSE 0 END AS sort_order FROM test_tbl ORDER BY sort_order ASC;
 col_id | col_char1 | sort_order 
--------+-----------+------------
     10 |           |          0
      2 | b         |          0
      3 | c         |          0
      4 | d         |          0
      6 | f         |          0
      7 | g         |          0
      9 | i         |          0
      1 | a         |          1
      8 | h         |          2
      5 | e         |          3
(10 行)

postgres=# SELECT col_id,col_char1 FROM test_tbl ORDER BY  col_id = 5 ASC;
 col_id | col_char1 
--------+-----------
     10 | 
      2 | b
      3 | c
      4 | d
      1 | a
      6 | f
      7 | g
      8 | h
      9 | i
      5 | e
(10 行)

postgres=# SELECT col_id,col_char1,col_id = 5 as expr FROM test_tbl ORDER BY expr ASC;
 col_id | col_char1 | expr 
--------+-----------+------
     10 |           | f
      2 | b         | f
      3 | c         | f
      4 | d         | f
      1 | a         | f
      6 | f         | f
      7 | g         | f
      8 | h         | f
      9 | i         | f
      5 | e         | t
(10 行)

CASEはもちろん、「列名=直値」も通ったよ!ただし、評価結果は「t(true)」「f(false)」で帰ってきます。

まとめ

・ORDER BY~には、関数や評価式が指定できる。
・その延長で、MySQLのFIELD関数が使えるので、任意の出力順を指定できる。(ORDER BY FIELDという構文じゃないよ)
MySQL以外の場合、CASE句に書き換えることが可能。


結論。SQLの仕様にCASEを取り込んだ人は神。以上!


【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)