SQLでグループごとにある最大値の行を取得する

  • 24
    いいね
  • 1
    コメント
この記事は最終更新日から1年以上が経過しています。

目的

データベース上のテーブルから、各グループごとにある最大値の行を取得する。

実現方法

以下の疑似コードに示されたSQLを発行する。

疑似コード
SELECT (取得したい列)
FROM テーブルA INNER JOIN (SELECT グループ列,MAX(最大値を求める列) AS 最大値
                           FROM テーブルA
                           GROUP BY グループ列) AS テーブルB
ON テーブルA.グループ列 = テーブルB.グループ列
AND テーブルA.最大値を求める列 = テーブルB.最大値;

次のようなテーブルがあるとする。

名前 年齢 都市
坂本 19 東京
中原 50 大阪
山岡 6 東京
赤橋 42 北海道
西 69 大阪

このテーブルを作るSQLは以下である。

名簿テーブルの作成
CREATE TABLE 名簿 (名前 VARCHAR(50), 年齢 INT, 都市 VARCHAR(100));

INSERT INTO 名簿 (名前,年齢,都市) VALUES ('坂本',19,'東京');
INSERT INTO 名簿 (名前,年齢,都市) VALUES ('中原',50,'大阪');
INSERT INTO 名簿 (名前,年齢,都市) VALUES ('山岡',6,'東京');
INSERT INTO 名簿 (名前,年齢,都市) VALUES ('赤橋',42,'北海道');
INSERT INTO 名簿 (名前,年齢,都市) VALUES ('西',69,'大阪');

このテーブルから都市ごとに最も年齢の高い人の行を得たいとする。

【取得したい結果】

名前 年齢 都市
坂本 19 東京
西 69 大阪
赤橋 42 北海道

この結果を得るには、まず「都市」列でグループ化し、「年齢」列の最大値を取得するSQLを書く。

クエリ
SELECT 都市,MAX(年齢)
FROM 名簿
GROUP BY 都市;

【結果】

都市 MAX(年齢)
東京 19
大阪 69
北海道 42

このままだと、「名前」列が取得できないので、結果として得られた表と元の表を結合する。

クエリ
SELECT 名簿A.名前,名簿A.年齢,名簿A.都市
FROM 名簿 AS 名簿A INNER JOIN (SELECT 都市,MAX(年齢) AS 最年長
                              FROM 名簿
                              GROUP BY 都市) AS 名簿B
ON 名簿A.都市 = 名簿B.都市
AND 名簿A.年齢 = 名簿B.最年長;

【結果】

名前 年齢 都市
坂本 19 東京
西 69 大阪
赤橋 42 北海道

求める結果が得られた。

失敗例

グループごとにまとめた段階で、「名前」列を取得しようとすると、Group By句に含まれていない列なので、取得に失敗する1

失敗例
SELECT 名前,都市,MAX(年齢)
FROM 名簿
GROUP BY 都市;

取得した行を更新するには?

UPDATE文の更新対象テーブルに、先ほど得られたテーブルを指定すれば良い。

クエリ
UPDATE 名簿 AS 名簿A INNER JOIN (SELECT 都市,MAX(年齢) AS 最年長
                                 FROM 名簿
                                 GROUP BY 都市) AS 名簿B
ON 名簿A.都市 = 名簿B.都市
AND 名簿A.年齢 = 名簿B.最年長
SET 名前 = concat(名前,'(古参)')

【結果】

名前 年齢 都市
坂本(古参) 19 東京
中原 50 大阪
山岡 6 東京
赤橋(古参) 42 北海道
西(古参) 69 大阪

確認環境

MySQL Community Server 5.6.28

注記


  1. MySQLの場合、拡張機能により結果は取得されるが、年齢が最大の行の名前は取得できない。都市のグループに属する名前が適当に!取得されるだけである。