【SQLレシピ】キー毎の最新バージョンのレコードをSELECTする

ネコニウム研究所

PCを利用したモノづくりに関連する情報を掲載するブログ

【SQLレシピ】キー毎の最新バージョンのレコードをSELECTする

2022-5-26 |

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関数に書き換えると社員番号毎の最古のレコードが出力される。

まとめ(感想文)

ジョバンニたちの勤務地は、著者的に岩手にするべきだったかもね!(名前的にはイタリアにするべき?)