あかつきのお宿

あれはもう五年……いや、六年ほど前になるのか。君たちにとってはついさっきの出来事かもしれんが……。

という感じでですね、もうかなーり前の話になるのですが、MySQLでGROUP BYとORDER BYを併用する際にはちょっと注意が必要だぞ〜ってな話をしたことがありました。

MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと

簡単におさらいすると、テーブルにこんな感じ（↓）のデータが入ってた場合に、投稿者（author_id）ごとに最新のデータを取得するためにGROUP BYとORDER BYを併用しても、欲しいデータは取れないぞっていう内容です。

//実行するSQL
SELECT * FROM table GROUP BY author_id ORDER BY created DESC

//テーブルの中身
+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  1 |     1     | title1 | text | 2012-10-01 12:00:00 | 
|  2 |     2     | title2 | text | 2012-10-02 12:00:00 | 
|  3 |     1     | title3 | text | 2012-10-03 12:00:00 | 
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
|  5 |     3     | title5 | text | 2012-10-05 12:00:00 | 
|  6 |     2     | title6 | text | 2012-10-06 12:00:00 | 
|  7 |     3     | title7 | text | 2012-10-07 12:00:00 | 
|  8 |     1     | title8 | text | 2012-10-08 12:00:00 | 
|  9 |     1     | title9 | text | 2012-10-09 12:00:00 | 
+----+-----------+--------+------+---------------------+

//欲しいデータ
+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  9 |     1     | title9 | text | 2012-10-09 12:00:00 | 
|  6 |     2     | title6 | text | 2012-10-06 12:00:00 | 
|  7 |     3     | title7 | text | 2012-10-07 12:00:00 | 
+----+-----------+--------+------+---------------------+

//実際に取れるデータ
+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
|  2 |     2     | title2 | text | 2012-10-02 12:00:00 | 
|  1 |     1     | title1 | text | 2012-10-01 12:00:00 | 
+----+-----------+--------+------+---------------------+

前回の記事ではサブクエリとかを使わなきゃ無理だっていう結論に至ったのですが、いくつかコメントをいただいた中で「HAVING句」を使ったらどうだっていうご意見があったんですよ。

SELECT * FROM table GROUP BY author_id HAVING created = MAX(created)

こうすれば投稿者ごとの最新データを取れるぞと。当時の僕はHAVING句という存在自体知らなかったので、「こいつはすげえ！　さっそく使ってみよう！」ってな感じでやってみたんですよ。

で、たぶんなんですが、当時自分で試した時は上手く取れたと思ったんですよね。もう何年も前のことなのでうろ覚えなのですが、確か「これからはHAVING句の時代だぜ！」的な結論が自分の中にあったはず。

でもつい先日、今度はHAVING句を使っても上手くいかねーぞっていうコメントをいただきまして……改めて自分でも検証してみたんですが、確かに上手くいきませんでした。

実際テーブルに上記と同じデータを突っ込んでHAVING句を使ってphpMyAdminでSQLを実行してみたんですが「返り値が空でした (行数 0)」という結果が出ました。上手くいくどころか一件のデータも取れなかった……。



HAVING句というのはグループ化したデータに対して条件を指定できる機能です。条件を指定するってところはWHERE句と似ていますが、グループ化したデータに対して効くってところがミソです。

例えば上記のデータに対して、以下のようなHAVING句を実行する。

SELECT * FROM table GROUP BY author_id HAVING id > MAX(author_id)

これは「autor_idでグループ化したレコードのうち、idがMAX(author_id)よりも大きいものだけを取得する」という意味になります。今回の場合、author_idは「1,2,3」の三つなので、MAX(author_id)は3ってことなりますね。つまりグループ化したデータのうち、idが3より大きいデータだけが取得されることになります。

先ほどauthor_idでグループ化した時に取れるデータはidが「1,2,4」の三つでした。このうちidが3より大きいデータはidが4の一つだけなので、結果はこうなります。

//SELECT * FROM table GROUP BY author_id HAVING id > MAX(author_id)
+----+-----------+--------+------+---------------------+
| id | author_id | title  | text |      created        |
+----+-----------+--------+----------------------------+
|  4 |     3     | title4 | text | 2012-10-04 12:00:00 | 
+----+-----------+--------+------+---------------------+

ここで気をつけたいのは、「MAX(author_id)」というのはグループ化されたデータの中で最大という意味ではなく、全レコードの中の最大値であるということです。

ってーことは「HAVING created = MAX(created)」とした場合、MAX(created)は「10月09日（2012-10-09 12:00:00）」となる。グループ化した時のデータ（idが1,2,4のデータ）のcreatedはそれぞれ「10月01日」「10月02日」「10月04日」なので、10月09日に該当するデータはありません。だから取得結果は0件になっちゃうわけですね。



何であの時は上手くいったと僕が判断したのか……真相は闇の中ですが、考えられるのは、phpMyAdminってデータが一件も取得できなかった場合、一つ前に取得した状態が画面に残ってるんですよね。バージョンによるかもしれませんが。

だからたぶん、最初に「SELECT * FROM table WHERE created IN(SELECT MAX(created) FROM table GROUP BY author_id)」とかで正しくデータを取得した後、HAVING句を使ったら結果は0件だったのに、「返り値が空でした」っていうメッセージを見落としてて「あれ？　できてんじゃん」って判断したのかもしれません。



実際やってみるとこんな感じ。一瞬、あたかもデータが正しく取れてるっぽく見えちゃいますよね。当時の僕はphpMyAdminもあまり使いこなせてないひよっこでしたから、きっとこんなミスをしていたのでしょう。うん、きっとそうだ。

いずれにせよHAVING句を使っても望み通りの結果が得られるとは限らないようなので、やっぱりサブクエリを使うなりする必要があるという結論に変わりはないみたいです。残念無念。