目的
データベース上のテーブルから、各グループごとにある最大値の行を取得する。
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)