【SQLレシピ】キー毎の最新バージョンのレコードをSELECTする
2022-5-26 | SQL
SQLでバージョン（更新日など）毎にレコードを作成してるテーブルで特定のキー毎に最新のバージョン（最終更新日）のみのSELECTを選択したい！
概要
この記事では、SQLでバージョン（更新日など）毎にレコードを作成してるテーブルで特定のキー毎に最新のバージョン（最終更新日）のみのレコードをSELECTする手順を掲載する。
仕様書
環境
- SQLite 3.35.5
- SQL Server 15.0.2000
手順書
下表のような人事情報を記録してるテーブルEmployeeを例にする。（サンプルなので正規化とか列名が不適切とかジョバンニが東京勤務とかは無視して下さい）
| 列名 | データ型 | 備考 |
|---|---|---|
| ID | INTEGER | ユニークな連番 |
| Date | INTEGER | 更新日（YYYYMMDD） |
| Number | INTEGER | 社員番号 |
| Name | TEXT | 氏名 |
| Department | TEXT | 部署 |
| Localtion | TEXT | 勤務地 |
登録してるデータは下記のとおり。
| ID | Date | Number | Name | Department | Localtion |
|---|---|---|---|---|---|
| 1 | 20010401 | 1 | ジョバンニ | 総務部 | 東京 |
| 2 | 20010401 | 2 | カムパネルラ | 営業部 | 東京 |
| 3 | 20010707 | 3 | ザネリ | 開発部 | 大阪 |
| 4 | 20020401 | 4 | マルソ | 製造部 | 仙台 |
| 5 | 20050401 | 2 | カムパネルラ | 営業部 | 仙台 |
| 6 | 20050901 | 3 | ザネリ | 製造部 | 大阪 |
| 7 | 20050901 | 4 | マルソ | 開発部 | 大阪 |
今回は、社員番号であるNumber列毎に更新日であるDate列が新しいレコードを選択するクエリを紹介する。
SELECT
ID,
Date,
Number,
Name,
Department,
Location
FROM EMPLOYEE T_ALL
WHERE Date=(
SELECT MAX(Date)
FROM EMPLOYEE T_NEW
WHERE T_ALL.Number=T_NEW.Number)
ORDER BY Number ASCクエリを実行すると下記のように社員番号毎の最新のレコードが出力される。
| ID | Date | Number | Name | Department | Localtion |
|---|---|---|---|---|---|
| 1 | 20010401 | 1 | ジョバンニ | 総務部 | 東京 |
| 5 | 20050401 | 2 | カムパネルラ | 営業部 | 仙台 |
| 6 | 20050901 | 3 | ザネリ | 製造部 | 大阪 |
| 7 | 20050901 | 4 | マルソ | 開発部 | 大阪 |
今回は、SQLiteベースで説明したため、更新日をINTEGER型にしてますが、SQL ServerなどのDate型が使えるデータベースであれば、Date型を更新日にしても上記のクエリはそのまま使える。というか、MAX関数を使えるデータ型であれば使える。
WHEREの中のサブクエリのWHEREでキーとなる列を指定する。例えばName列を選択すると名前毎の最新版が選択されるが、同姓同名の社員がいた場合、意図した通りの動作にならなくなるので、ユニークになってる列を指定する。
MAX関数の部分をMIN関数に書き換えると社員番号毎の最古のレコードが出力される。
まとめ（感想文）
ジョバンニたちの勤務地は、著者的に岩手にするべきだったかもね！（名前的にはイタリアにするべき？）
