こんな感じにデータを持ってる sample_table があるとする。
+----+----------+------------+---------+
| id | group_id | updated_at | comment |
+----+----------+------------+---------+
| 1 | 1 | 2013-12-01 | C |
| 2 | 2 | 2013-12-01 | A |
| 3 | 1 | 2013-12-02 | B |
| 4 | 2 | 2013-11-30 | D |
+----+----------+------------+---------+MySQL で同じデータを作成したければ、以下の SQL で再現できる。
CREATE TABLE sample_table (
id int(11) NOT NULL,
group_id int(11) NOT NULL,
updated_at date NOT NULL,
comment varchar(60) NOT NULL
);
INSERT INTO sample_table VALUES
(1, 1, '2013-12-01', 'C'),
(2, 2, '2013-12-01', 'A'),
(3, 1, '2013-12-02', 'B'),
(4, 2, '2013-11-30', 'D');こんなデータのとき、同じグループ内であるフィールドが最大（あるいは最小）のレコードを取得したい、ということがたまにある。例では、group_id それぞれについて、 updated_at が最新のレコード、即ち comment が A および B のレコードを取得したいとしよう。
さて、どんな SQL を書けばよいだろう？
GROUP BY をする
なになに、更新日が最新とな。簡単やろ。
SELECT group_id, MAX(updated_at)
FROM sample_table
GROUP BY group_id;
+----------+-----------------+
| group_id | MAX(updated_at) |
+----------+-----------------+
| 1 | 2013-12-02 |
| 2 | 2013-12-01 |
+----------+-----------------+なるほど、正しい。だが待ってほしい。それぞれのレコードの ID とコメントはどこへ行ったのだろう？
仕方ない、じゃあ ID とコメントも SELECT に含めよう。
SELECT id, group_id, MAX(updated_at), comment
FROM sample_table
GROUP BY group_id;残念。この SQL は不正である。GROUP BY に指定していないフィールドを集計関数を通さずに取得することはできないからだ。もっともこの SQL は MySQL であればエラーなしに動いてしまう。
+----+----------+-----------------+---------+
| id | group_id | MAX(updated_at) | comment |
+----+----------+-----------------+---------+
| 1 | 1 | 2013-12-02 | C |
| 2 | 2 | 2013-12-01 | A |
+----+----------+-----------------+---------+なんだ、できるじゃないか。MySQL 偉い。と、言いたいところだが、残念ながら取得できているデータは意図したものではない。データベースの機嫌(!)によってこの結果は変動し得るし、事実欲しいレコードは ID 2 と 3 のレコードのはずだったのだがそうはなっていない。
うむ。困った。
ソートする
欲しいのは GROUP BY したその中の要素をソートした最初のレコードなわけだから、それを素直に書いてみたらどうだろう。
SELECT *
FROM sample_table AS m
WHERE id = (
SELECT id
FROM sample_table AS s
WHERE m.group_id = s.group_id
ORDER BY s.updated_at DESC
LIMIT 1
);MySQL で実行すれば期待する結果が得られる。ただしこのクエリは、以下のような欠点がある。
- 主キーが複合キーな場合に類似のサブクエリを複数記述する必要がある。
LIMIT句をサポートしないデータベースエンジンが存在するため汎用性に欠ける（同等の意味を実現するクエリ自体は書けるだろうが）。- 同一グループ内に「最大」を意味するレコードが複数存在する場合、そのうちひとつが勝手に選択される。
最大値を得るサブクエリを書く
updated_at がグループ内で最大となるレコードがほしいので、そのグループ内の最大の updated_at をサブクエリで求め、それで検索をするのはどうだろう。
SELECT *
FROM sample_table AS m
WHERE updated_at = (
SELECT MAX(updated_at)
FROM sample_table AS s
WHERE m.group_id = s.group_id
);こちらでも期待する結果が得られる。ただしこのクエリにも以下の欠点がある。
- 比較が 1 フィールドで済むことを前提としているので、複数必要な場合は利用できない。
なお、こちらのクエリでは、同一グループ内に「最大」を意味するレコードが複数存在していると、そのすべてが選択される。
発想を転換する
発想を転換してみよう。「最大」という概念を「該当レコード以外により大きいレコードがない」と考えれば、以下のように書ける。
SELECT *
FROM sample_table AS m
WHERE NOT EXISTS (
SELECT 1
FROM sample_table AS s
WHERE m.group_id = s.group_id
AND m.updated_at < s.updated_at
);このクエリは「標準SQL範囲内」「主キーに依存しない」という点で、これまでのクエリよりもエレガントである。もっとも、この形式でも比較フィールドが複数の場合は OR (m.updated_at = s.updated_at AND m.second_field < s.second_field) のような煩雑な形になるのが玉にキズではある。
なお、こちらのクエリでも、同一グループ内に「最大」のレコードが複数存在していると、そのすべてが選択される。
＃人気の記事
＃タグ
