7-45 部分一致する最大のレコードと結合

SQLパズル

Table745    マスタ745
ID          ID   Val1  Val2
-------     ---  ----  ----
abczzz      ab      3  AAAA
abcdzz      cd      6  BBBB
abcdef      def     9  CCCC

Table745と、マスタ745を
IDが部分一致するなかで、Val1が最大のデータと結合させる。

出力結果
ID      Val1  Val2
------  ----  ----
abczzz     3  AAAA
abcdzz     6  BBBB
abcdef     9  CCCC

データ作成スクリプト

create table Table745(ID char(6));
insert into Table745 values('abczzz');
insert into Table745 values('abcdzz');
insert into Table745 values('abcdef');

create table マスタ745(
ID varchar2(3),
Val1 number(1),
Val2 char(4));

insert into マスタ745 values('ab' ,3,'AAAA');
insert into マスタ745 values('cd' ,6,'BBBB');
insert into マスタ745 values('def',9,'CCCC');
commit;

SQL

--■■■Row_Number関数を使う方法■■■
select ID,Val1,Val2
from (select a.ID,b.Val1,b.Val2,
      Row_Number() over(partition by a.ID order by Val1 desc) as rn
        from Table745 a,マスタ745 b
       where instr (a.ID,b.ID) > 0)
 where Rn = 1
order by ID;

--■■■Last_Value関数を使う方法■■■
select a.ID,b.Val1,b.Val2
from Table745 a,マスタ745 b
where b.RowID = (select distinct
                 Last_Value(c.RowID)
                 over(order by Val1 Rows between Unbounded Preceding and Unbounded Following)
                 from マスタ745 c
                 where instr(a.ID,c.ID) > 0)
order by a.ID;

解説

distinctを使って、Last_Value関数の結果の重複を排除して、
スカラー問い合わせになるようにし、
RowIDを使って、自己結合してます。

---