Oracle：グループごとに最大日付の行を1件取得

質問

Oracle11gで、以下のようなテーブルから、
「IDごとに、DTが最大のレコードのBUMONを1件ずつ取得」　したいです。

＜テーブルTB＞
ID DT BUMON
-------------
1 9/1 A
1 9/2 A
1 9/3 B ★Bを取得したい
2 9/4 C
2 9/5 C ★Cを取得したい
3 9/1 D
3 9/3 E
3 9/3 E ★Eを取得したい

＜取得したいデータ＞
BUMON
-------
B
C
E

＜考えたSQL＞
select
distinct A.BUMON
from
(select ID, DT, max(DT) over(partition by ID) as MAXDT, BUMON from TB) A,
(select ID, DT, max(DT) over(partition by ID) as MAXDT, BUMON from TB) B
where
A.ID = B.ID and
A.DT = B.MAXDT
;

上記のような方法を考えましたが、取得できますでしょうか？
現在、SQL実行環境がない場所にいるのですが、急ぎで確認したく、
どなたかお知恵を拝借できませんでしょうか？？
また、可能であれば、★のレコード全体を取得するSQLもご教授いただきたく存じます。

＜★のレコード全体を取得した結果＞
ID DT BUMON
-------------
1 9/3 B
2 9/5 C
3 9/3 E

回答No.2

yamada_g

select ID, DT, BUMON
from (
select ID, DT, BUMON, row_number() over(partition by ID order by DT desc) rnum
from TB
)
where rnum = 1;

なんてのでもいいと思います。

回答No.1

root139

ORACLE が手元に有りませんので、以下はPostgreSQLで確認しましたが、特にベンダの機能を使っていませんので大丈夫だと思います。

提示されているSQLのSELECT句を下記の様に変えるだけで、ご希望のものが取得できるかと。(MAX値のカラムも含まれますが・・・)

distinct A.BUMON
↓
distinct A.*

更に結合する片方のテーブルはサブクエリにする必要は無いので簡単に出来ます。

(select ID, DT, max(DT) over(partition by ID) as MAXDT, BUMON from TB) A,
↓
TB A,

更に INNER JOIN を使って書き直すことも出来ます。

select
distinct A.*
from
TB A INNER JOIN
(select ID, DT, max(DT) over(partition by ID) as MAXDT, BUMON from TB) B
ON(A.ID = B.ID AND A.DT = B.MAXDT)
;

別のアプローチとしては相関サブクエリを使って絞り込む方法も有ります。

select DISTINCT * from TB A
where not exists (select * from TB B where A.ID = B.ID and A.DT < B.DT)
;

参考URL：

http://codezine.jp/article/detail/907

お礼

ありがとうございます！
とてもわかりやすいご説明、心から感謝いたします！