SQLで特定のまとまりごとの最新の値のrecordが欲しい場合は条件適用後の自身をinner joinすれば良い

背景

SQLでの特定のまとまりごとの最新の値を持つrecordの取得に手間取ったので、備忘録として記事に残します。

参考にした投稿はこちらです。
Return value at max date for a particular id

使ったもの

mariadb

SELECT VERSION();
+-------------------------------------+
| VERSION()                           |
+-------------------------------------+
| 10.8.3-MariaDB-1:10.8.3+maria~jammy |
+-------------------------------------+
1 row in set (0.006 sec)

mariadbに依存する機能呼び出しは使っていないので、多分他のDBでも同じ処理ができると思います。

group byとmaxで条件を抽出した自身をinner joinしてrecordを取得

この記事で紹介したいSQLはこちらです。
このSQLはdevice_fileテーブルからdevice_idごとにrecorded_atが最新のrecordを取得します。

SELECT device_file.* FROM device_file
INNER JOIN
(SELECT max(recorded_at) max_recorded_at, device_id
 FROM device_file GROUP BY device_id) df2
ON device_file.recorded_at = df2.max_recorded_at
AND device_file.device_id = df2.device_id;

期待通りに値を取れました。

おわり

selectとgroup byだけで何とかならないか四苦八苦しましたが、joinを使えば期待通りの値を取得できると分かって良かったです。

他に効率が良いSQLがあれば、コメントなどで教えていただけると嬉しいです。

参考

こちらの投稿でinner joinを使う方法を把握しました。
Return value at max date for a particular id