同一グループの中で最大のレコードを取得する SQL を書く


2014年 10月 28日

こんな感じにデータを持ってる 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) のような煩雑な形になるのが玉にキズではある。

なお、こちらのクエリでも、同一グループ内に「最大」のレコードが複数存在していると、そのすべてが選択される。