指定された日付までの日付を持つ値のうち、グループ毎に最新の値を取得したい

Question

実現したいこと

次のようなデータがある場合

id	group_id	value_number	registed
1	1	11	2025-08-01
2	2	21	2025-08-01
3	1	12	2025-10-01
4	1	13	2025-08-01
5	1	14	2025-08-01
6	2	22	2025-09-01

id：6のregistedを「2025-10-01」から「2025-09-01」に修正しました。

指定された日付までの登録データの中で、グループ毎に最新の値が取得したいです。
同一のグループで同一の日付に複数の登録がある場合は、後から登録されたもの（idが大きいもの）を取得したいと考えています。
なお、登録は必ずしも日付順ではない為、idの大きいなものにより古い日付のデータが登録される事もあります。

日付の閾値として「2025-10-01」が指定された場合は以下のような結果を期待します。

id	group_id	value_number	registed
3	1	12	2025-10-01
6	2	22	2025-09-01

id：6の内容修正に伴い期待する取得結果も修正しました

日付の閾値として「2025-08-01」が指定された場合は以下のような結果を期待すます。

id	group_id	value_number	registed
5	1	14	2025-08-01
2	2	21	2025-08-01

このような結果を期待していますが、うまく取得する方法が思いつきません。

発生している問題・分からないこと

SQLだけで結果を取得する事が難しい場合は、諦めて一度全件取得しプログラム側の個別の集計処理も考えています。

何か良い方法はありますでしょうか。

試したこと・調べたこと

• teratailやGoogle等で検索した

上記の詳細・結果

多くの場合で、登録されるデータとして必ず新しいものが後に登録される（idが大きい）ケースはあり、参考になるのですが、今回のように必ずしもidが大きなものが新しい日付でない場合は上手く取得できませんでした。

試したSQL（微妙に上手くいっていないケース）

SELECT
`group_id`,
`id`,
MAX(registed) AS `latestDate`
FROM
`hoge`
WHERE
`registed` <= '2025-08-01'
GROUP BY
`group_id`
;

これですと2つ目のケースのグループ2が正常に新しいレコードが取得できません。
なお、MySQLでのSTRICT制約は外しています。

補足1

元データの id：6の内容修正に伴い期待する取得結果も修正しました。
閾値として設定された日付より前に登録されたデータの内、より後に登録されたものを取得したい為。

Comments

[shiketa]

読み違えていました。取り下げます。

[Auxo]

質問ありがとうございます。

＞「日付の閾値として「2025-10-01」が指定された場合」には、2025-10-01が含まれ、
＞「日付の閾値として「2025-09-01」が指定された場合」には、2025-09-01は含まないのですね？

2つ目のケースは私の閾値の指定間違いでした。
「2025-09-01」ではなく「2025-08-01」でした。
間違った表記となってしまい申し訳ございませんでした。

閾値となっている日付は、その値の有効な日付です。
履歴を持ちたいので「上書き」ではなく「追加」としています。
本来であれば「日付」ではなく「日時」として時間も管理すべきなのですが、
値の受付（入力）が日付単位の為、値の受け入れとしては単純に後勝ち方式となっています。

そのため、どうにも閾値である日付が同日となっているケースでより新しいものを上手く撮れないという状況です。

[jimbe]

既存回答のコメントに「微妙にうまくいかないSQL」を提示されていますが、それらは質問本文（"試したこと"）に結果（欲しい結果と比較し易い形で）と共に書いて置いたほうが良いのではないでしょうか。

[shiketa]

「日付の閾値として「2025-10-01」が指定された場合は以下のような結果を期待し」ているのであれば、わかる気がしますが、なぜid=3が選択されるのかが理解できていません。

|--|--------|------------|--------|
|5|1|14|2025-08-01|
|6|2|22|2025-09-01|

なにか大事な要件を読み取れていないのでしょうね。わたしが。

[Auxo]

id=5ではなくid=3が選択される理由は、閾値の範囲内（2025-10-01以前なので「<=」）である場合は、第一条件としては日付（registed）がより新しいものが選択されるイメージです。
その上でもし同一の日付を持つものが複数ある場合は、第二条件としてより後に登録されたデータ（idが大きなもの）が選択されるイメージでおります。

閾値に対する判定（「以前」として判断）を明示的にしていませんでした。
失礼いたしました。

Answer 1

ウィンドウ関数が利用可能なバージョン・環境であれば、ROW_NUMBER関数を使う方法が最も分かりやすいと思います。

SELECT id, group_id, value_number, registed
FROM (
  SELECT 
    id, group_id, value_number, registed,
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY registed DESC, id DESC) AS rn
  FROM sample_table
  WHERE registed <= '2025-10-01'
) AS T
WHERE rn = 1;

一方、ウィンドウ関数が利用できないやや古いバージョンや環境の場合は、サブクエリを利用する方法もあります。

SELECT *
FROM sample_table AS S1
WHERE S1.registed <= '2025-10-01'
AND NOT EXISTS (
  SELECT *
  FROM sample_table AS S2
  WHERE S2.registed <= '2025-10-01'
  AND S1.group_id = S2.group_id
  AND (S1.registed, S1.id) < (S2.registed, S2.id)
)

なお、検証に使用したDDL・DMLは以下の通りです。

CREATE TABLE sample_table (
    id INT PRIMARY KEY,
    group_id INT,
    value_number INT,
    registed DATE
);

INSERT INTO sample_table (id, group_id, value_number, registed) VALUES
(1, 1, 11, '2025-08-01'),
(2, 2, 21, '2025-08-01'),
(3, 1, 12, '2025-10-01'),
(4, 1, 13, '2025-08-01'),
(5, 1, 14, '2025-08-01'),
(6, 2, 22, '2025-09-01');

Comments

[Auxo]

回答ありがとうございます。
ご提示いただいた内容で無事解決いたしました。

内容をあらためて確認しつつ、勉強させていただきます。
特にウィンドウ関数はあまり使った事がないので、
良い機会なのでもう一度勉強し直したいと思います。

なお、ベストアンサーについては申し訳ございませんが、
投稿時間の早かった方に付けさせていただきました。

Answer 2

postgres で試してますが、 shiketa さんの定義コードを拝借して(register text→registed date)

create table hoge (
 id int,
 group_id int,
 value_number int,
 registed date
);

insert into hoge values
 (1, 1, 11, '2025-08-01'),
 (2, 2, 21, '2025-08-01'),
 (3, 1, 12, '2025-10-01'),
 (4, 1, 13, '2025-08-01'),
 (5, 1, 14, '2025-08-01'),
 (6, 2, 22, '2025-09-01');

そのまんま書いたら

SELECT c.*
FROM hoge AS c
JOIN (SELECT MAX(a.id) AS id
      FROM hoge AS a
      JOIN (SELECT group_id, MAX(registed) AS registed
            FROM hoge
            WHERE registed <= '2025-08-01'
            GROUP BY group_id) AS b
      ON a.group_id=b.group_id AND a.registed=b.registed
      GROUP BY a.group_id, a.registed) AS d
ON c.id=d.id

 id | group_id | value_number |  registed
----+----------+--------------+------------
  2 |        2 |           21 | 2025-08-01
  5 |        1 |           14 | 2025-08-01
(2 行)

7行目を '2025-10-01' に変えると

 id | group_id | value_number |  registed
----+----------+--------------+------------
  3 |        1 |           12 | 2025-10-01
  6 |        2 |           22 | 2025-09-01
(2 行)

となりますが、如何でしょう。

Comments

[Auxo]

回答ありがとうございます。
ご提示いただいた内容で無事解決いたしました。

内容をあらためて確認しつつ、勉強させていただきます。

Answer 3

簡単にやるならこんな感じでいけると思います

select * from tbl inner join (
select max(id) as id,group_id,register
from tbl
group by group_id,register
having register='2025-10-01'
) as sub using(id,group_id,register)

Comments

[Auxo]

私の取得元データの例示が悪く、問題点がわかり難かった為、取得元データを修正しました。

例示いただいたhaving句の指定ですと、その日のデータに限定されてしまいます。
期待するものとしては、指定された日付以前の「registed」を持つレコードの中で、最新（idの大きいなもの）をグループ毎に取得できないかな、という所です。

イメージとしては「GROUP BY」を「registed」にかけて、同一だった時により後のidが選択されれば良いのですが、実際にはそういった動きにはならず、強引に先にidで降順にソートした結果をGROUP BYしたりもしましたが、やはりidの小さいものが取得されてしまうようでした。

以下、微妙に上手くいかないSQL文です。

SELECT
`group_id`,
`id`,
MAX(registed) AS `latestDate`
FROM
`hoge`
WHERE
`registed` <= '2025-08-01'
GROUP BY
`group_id`
;

これですと2つ目のケースのグループ2が正常に新しいレコードが取得できません。
なお、MySQLでのSTRICT制約は外しています。

[yambejp]

新しい条件になって「最新の値」の定義があいまいです
registerdの最大値でさらにその中の最大のidを取りたいのでしょうか？

[Auxo]

＞registerdの最大値でさらにその中の最大のidを取りたいのでしょうか？

ご指摘のとおりです。
registedが閾値以内の条件でもっとも新しく、且つ、最大のidとなります。

なお、実現できるSQL文を無事確認する事ができました。
色々と私の提示内容が曖昧でご迷惑をおかけ致しました。

コメントありがとうございました。

Answer 4

create table hoge (
 id int,
 group_id int,
 value_number int,
 register text
);

insert into hoge values
 (1, 1, 11, '2025-08-01'),
 (2, 2, 21, '2025-08-01'),
 (3, 1, 12, '2025-10-01'),
 (4, 1, 13, '2025-08-01'),
 (5, 1, 14, '2025-08-01'),
 (6, 2, 22, '2025-10-01')
;

with
aa as (
  select 
    group_id,
    max(value_number) as value_number,
    register 
  from hoge
  group by group_id, register 
)
,
bb as (
  select hoge.* from hoge, aa
    where hoge.group_id = aa.group_id
      and hoge.value_number = aa.value_number
      and hoge.register = aa.register
)
select * from bb
  where register <= '2025-10-01'
  order by 4, 1
;

結果。

id	group_id	value_number	register
2	2	21	2025-08-01
5	1	14	2025-08-01
3	1	12	2025-10-01
6	2	22	2025-10-01

「閾値」のあたりは、最後のwher節でどうにでもなるかと。

Comments

[yambejp]

registerはdate型を指定したよいかもしれません

[Auxo]

やはり「GROUP BY」のような形でグループ毎に1つの結果を求める事は難しいのでしょうか。

微妙に上手くいかないSQL文として以下の様なところまでは実現できております。

SELECT
`group_id`,
`id`,
MAX(registed) AS `latestDate`
FROM
`hoge`
WHERE
`registed` <= '2025-08-01'
GROUP BY
`group_id`
;

この文ですと同一の「registed」を持つ場合に「id」が古いもの（小さいなid）が取得されてしまっており、それを新しいものに何とかできないかと試行錯誤しております。
試しに、SORTで降順にした結果に対して実行してみましたが、結果は変わりませんでした。

[shiketa]

> registerはdate型を指定したよいかもしれません
はい。手抜きです f(^^;