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

More than 1 year has passed since last update.


目的

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


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)