SQL

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

目的

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

GROUP BYを使った方法

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

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

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

Name Age City
坂本 19 東京
中原 50 大阪
山岡 6 東京
赤橋 42 北海道
西 69 大阪

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

名簿テーブルの作成
CREATE TABLE Member (Name VARCHAR(50), Age INT, City VARCHAR(100));

INSERT INTO Member (Name,Age,City) VALUES ('坂本',19,'東京');
INSERT INTO Member (Name,Age,City) VALUES ('中原',50,'大阪');
INSERT INTO Member (Name,Age,City) VALUES ('山岡',6,'東京');
INSERT INTO Member (Name,Age,City) VALUES ('赤橋',42,'北海道');
INSERT INTO Member (Name,Age,City) VALUES ('西',69,'大阪');

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

【取得したい結果】

Name Age City
坂本 19 東京
西 69 大阪
赤橋 42 北海道

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

クエリ
SELECT
    City,
    MAX(Age)
FROM
    Member
GROUP BY
    City;

【結果】

City MAX(Age)
東京 19
大阪 69
北海道 42

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

クエリ
SELECT
    MemberA.Name,
    MemberA.Age,
    MemberA.City
FROM
    Member AS MemberA
    INNER JOIN (SELECT
                    City,
                    MAX(Age) AS MaxAge
                FROM
                    Member
                GROUP BY
                    City) AS MemberB
    ON MemberA.City = MemberB.City
    AND MemberA.Age = MemberB.MaxAge;

【結果】

Name Age City
坂本 19 東京
西 69 大阪
赤橋 42 北海道

求める結果が得られた。

失敗例

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

失敗例
SELECT
    Name,
    MAX(Age),
    City
FROM
    Member
GROUP BY
    City;

PARTITION BYを使った方法

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

疑似コード
SELECT
    ランキングテーブル.取得したい列
FROM
    (
    SELECT
        ROW_NUMBER() OVER (
            PARTITION BY
                グループ化する列
            ORDER BY
                (最大値を求める列) DESC
        ) AS ランキング,
        (取得したい列)
    FROM
        テーブルA
    ) AS ランキングテーブル
WHERE
    ランキングテーブル.ランキング = 1

以下のテーブルから、都市ごとに一番年齢が高い人の行を取得したいとする。

Name Age City
坂本 19 東京
中原 50 大阪
山岡 6 東京
赤橋 42 北海道
西 69 大阪

1. 都市ごとに年齢のランキングを作る。

年齢のランキング
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY
            City
        ORDER BY
            Age DESC
    ) AS OldRank,
    Name,
    Age,
    City
FROM
    Member
  • SQLの実行結果
OldRank Name Age City
1 西 69 大阪
2 中原 50 大阪
1 坂本 19 東京
2 山岡 6 東京
1 赤橋 42 北海道

2. ランキング1位の行を抜き出す

「1. 都市ごとに年齢のランキングを作る。」で得られた結果表から、ランキング1位の行を取得する。

ランキング1位を取得
SELECT
    OldMember.Name,
    OldMember.Age,
    OldMember.City
FROM
    (     -- ランキング表
    SELECT
        ROW_NUMBER() OVER (
            PARTITION BY
                City
            ORDER BY
                Age DESC
        ) AS OldRank,
        Name,
        Age,
        City
    FROM
        Member
    ) AS OldMember
WHERE
    OldMember.OldRank = 1
  • SQLの実行結果
Name Age City
西 69 大阪
坂本 19 東京
赤橋 42 北海道

求める結果が得られた。

確認環境

Windows 10 Home
SQL Server 2017 Express Edition (64-bit)