今日はちょっとだけはまったSQLの話し。例えばブログのエントリーについたコメントが新しい順にエントリーをソートしたいときを考えてみます。

テーブル構成はこんな感じ。

CREATE TABLE `entry` (
  `id` int(11) NOT NULL auto_increment,
  `title` text,
  `body` text,
  `created_at` datetime default NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `comment` (
  `id` int(11) NOT NULL auto_increment,
  `entry_id` int,
  `body` text,
  `created_at` datetime default NULL,
  PRIMARY KEY  (`id`)
);

で、入ってるデータはこんなん。

mysql> select * from entry;
+----+--------+-------+---------------------+
| id | title  | body  | created_at          |
+----+--------+-------+---------------------+
|  1 | title1 | body1 | 2009-01-01 12:00:00 | 
|  2 | title2 | body2 | 2009-01-02 12:00:00 | 
|  3 | title3 | body3 | 2009-01-03 12:00:00 | 
+----+--------+-------+---------------------+

mysql> select * from comment;
+----+----------+----------+---------------------+
| id | entry_id | body     | created_at          |
+----+----------+----------+---------------------+
|  1 |        1 | comment1 | 2009-01-04 12:00:00 | 
|  2 |        2 | comment2 | 2009-01-05 12:00:00 | 
|  3 |        3 | comment3 | 2009-01-06 12:00:00 | 
|  4 |        3 | comment4 | 2009-01-07 12:00:00 | 
|  5 |        2 | comment5 | 2009-01-08 12:00:00 | 
|  6 |        1 | comment6 | 2009-01-09 12:00:00 | 
+----+----------+----------+---------------------+

これを、新しいコメントがついた順にエントリーをソートしようとすると、entry_idは1,2,3の順にならないといけないですよね。

で、下記のようなSQLを書いてみたら全然期待しない結果になりました。

mysql> select * from comment group by entry_id order by created_at desc; 
+----+----------+----------+---------------------+
| id | entry_id | body     | created_at          |
+----+----------+----------+---------------------+
|  3 |        3 | comment3 | 2009-01-06 12:00:00 | 
|  2 |        2 | comment2 | 2009-01-05 12:00:00 | 
|  1 |        1 | comment1 | 2009-01-04 12:00:00 | 
+----+----------+----------+---------------------+

これはorder byよりgroup byが先に処理されるからです。以下の様に書けばよさげ。

mysql> select id,entry_id,body,created_at,max(created_at) as max_date from comment group by entry_id order by max_date desc;
+----+----------+----------+---------------------+---------------------+
| id | entry_id | body     | created_at          | max_date            |
+----+----------+----------+---------------------+---------------------+
|  1 |        1 | comment1 | 2009-01-04 12:00:00 | 2009-01-09 12:00:00 | 
|  2 |        2 | comment2 | 2009-01-05 12:00:00 | 2009-01-08 12:00:00 | 
|  3 |        3 | comment3 | 2009-01-06 12:00:00 | 2009-01-07 12:00:00 | 
+----+----------+----------+---------------------+---------------------+

selectするときにソートするカラムの最大値を取得しておいて、その値でソートします。こういうのってデータによっては間違ったSQLでも期待通りの値が返ってくることがあるから要注意。

ORMで育ったゆとり世代でホントすいません。