GRPUP BY で集計→ max のレコードを抽出（MySQL/MariaDB）のための備忘録

見よう見まねでSQL。



このような「食べ放題」テーブルで、
```
+----+--------------+------------+--------------+
| ID | 学生番号     | 日付       | おにぎり     |
+----+--------------+------------+--------------+
|  1 | 100          | 2024-03-27 |            5 |
|  2 | 100          | 2024-03-22 |            4 |
|  3 | 101          | 2024-03-10 |            7 |
|  4 | 102          | 2024-03-21 |           10 |
|  5 | 101          | 2024-03-15 |            3 |
+----+--------------+------------+--------------+

```
最後にチャレンジしたスコアを学生ごとに出したい。

```


+----+--------------+------------+--------------+
| ID | 学生番号     | 日付       | おにぎり     |
+----+--------------+------------+--------------+
|  1 | 100          | 2024-03-27 |            5 |
|  4 | 102          | 2024-03-21 |           10 |
|  5 | 101          | 2024-03-15 |            3 |
+----+--------------+------------+--------------+
```
上記のようなものを出すには、
```
SELECT ID,  学生番号,max(日付),おにぎり  FROM 食べ放題
            GROUP BY 学生番号
```
でいいかな・・・
```
+----+--------------+-------------+--------------+
| ID | 学生番号     | max(日付)   | おにぎり     |
+----+--------------+-------------+--------------+
|  1 | 100          | 2024-03-27  |            5 |
|  3 | 101          | 2024-03-15  |            7 |
|  4 | 102          | 2024-03-21  |           10 |
+----+--------------+-------------+--------------+
```

 うーん？

# 環境


- mysql  Ver 15.1 Distrib 10.6.16-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper
- 母艦 Ubuntu22.04 LTS

# 解決方法
調べてみたらいろいろ方法があるらしい

## INNTER JOIN を使う方法
```
 SELECT ID,学生番号 ,日付 ,おにぎり
  FROM 食べ放題 AS 食べ放題A
  INNER JOIN 
   (
    SELECT max(日付) AS 日付B ,学生番号 AS 学生番号B FROM 食べ放題
     GROUP BY 学生番号B
   )
   AS 食べ放題B
  ON 食べ放題A.学生番号=食べ放題B.学生番号B
  AND 食べ放題A.日付=食べ放題B.日付B;
```
```
+----+--------------+------------+--------------+
| ID | 学生番号     | 日付       | おにぎり     |
+----+--------------+------------+--------------+
|  1 | 100          | 2024-03-27 |            5 |
|  4 | 102          | 2024-03-21 |           10 |
|  5 | 101          | 2024-03-15 |            3 |
+----+--------------+------------+--------------+
```

## PARTITON BY を使う方法
GROUP BY を使わない方法。
Rank フィールドを持ったデータを作って、 PARTITON BY で Rank = 1 を抽出する。
こっちがわかりやすいかな。

```
SELECT 食べ放題Member.ID,食べ放題Member.おにぎり,食べ放題Member.学生番号,食べ放題Member.日付
FROM 
  ( SELECT ROW_NUMBER() OVER (
    PARTITION BY 学生番号
    ORDER BY 日付 DESC
  ) AS 日付Rank,
   ID,
   おにぎり,
   学生番号,
   日付
   FROM  食べ放題
   ) AS 食べ放題Member
   WHERE 食べ放題Member.日付Rank = 1
```
 
```
+------+--------------+--------------+------------+
| ID   | おにぎり     | 学生番号     | 日付       |
+------+--------------+--------------+------------+
|    1 |            5 | 100          | 2024-03-27 |
|    5 |            3 | 101          | 2024-03-15 |
|    4 |           10 | 102          | 2024-03-21 |
+------+--------------+--------------+------------+
```

うまくいきました。

### 抽出元を絞り込む
```
SELECT 食べ放題Member.ID,食べ放題Member.おにぎり,食べ放題Member.学生番号,食べ放題Member.日付
FROM 
  ( SELECT ROW_NUMBER() OVER (
    PARTITION BY 学生番号
    ORDER BY 日付 DESC
  ) AS 日付Rank,
   ID,
   おにぎり,
   学生番号,
   日付
   FROM  食べ放題 WHERE 日付<'2024/03/25'
   ) AS 食べ放題Member
   WHERE 食べ放題Member.日付Rank = 1
```
```
+------+--------------+--------------+------------+
| ID   | おにぎり     | 学生番号     | 日付       |
+------+--------------+--------------+------------+
|    2 |            4 | 100          | 2024-03-22 |
|    5 |            3 | 101          | 2024-03-15 |
|    4 |           10 | 102          | 2024-03-21 |
+------+--------------+--------------+------------+
```