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

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

# GROUP BYを使った方法
以下の疑似コードに示されたSQLを発行する。

```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は以下である。

```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を書く。

```sql:クエリ
SELECT
    City,
    MAX(Age)
FROM
    Member
GROUP BY
    City;
```
【結果】

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

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

```sql:クエリ
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句に含まれていない列なので、取得に失敗する。

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

# PARTITION BYを使った方法
以下の疑似コードに示されたSQLを発行する。

```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. 都市ごとに年齢のランキングを作る。

```sql:年齢のランキング
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位の行を取得する。

```sql:ランキング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)