10-68 グループで最大値の行のフラグオフ

SQLパズル

XXXテーブル
AAA        BBB   CCC     DDD       EEE        FFF       FLG
---------  ----  ------  --------  ---------  --------  ----
111111111  YAMA  東京都  千代田区   ○○1-1-1  20050101  null
111111111  YAMA  東京都  千代田区   ○○1-1-1  20041201  null
111111111  YAMA  東京都  千代田区   ○○1-1-1  20041115  null
222222222  KAWA  東京都  千代田区   ○○2-1-1  20050102  null
222222222  KAWA  東京都  千代田区   ○○2-1-1  20060103  null

XXXテーブルの、
AAA、BBB、CCC、DDD、EEEごとに、
FFFが最大の行のFLGを0に、
FFFが最大でない行のFLGを1に更新する

更新結果
AAA        BBB   CCC     DDD       EEE        FFF       FLG
---------  ----  ------  --------  ---------  --------  ----
111111111  YAMA  東京都  千代田区   ○○1-1-1  20050101     0
111111111  YAMA  東京都  千代田区   ○○1-1-1  20041201     1
111111111  YAMA  東京都  千代田区   ○○1-1-1  20041115     1
222222222  KAWA  東京都  千代田区   ○○2-1-1  20050102     1
222222222  KAWA  東京都  千代田区   ○○2-1-1  20060103     0

データ作成スクリプト

create table XXX(
AAA varchar2(9),
BBB varchar2(6),
CCC varchar2(10),
DDD varchar2(10),
EEE varchar2(20),
FFF varchar2(8),
FLG varchar2(1));

insert into XXX values('111111111','YAMA','東京都','千代田区','○○1-1-1','20050101',null);
insert into XXX values('111111111','YAMA','東京都','千代田区','○○1-1-1','20041201',null);
insert into XXX values('111111111','YAMA','東京都','千代田区','○○1-1-1','20041115',null);
insert into XXX values('222222222','KAWA','東京都','千代田区','○○2-1-1','20050102',null);
insert into XXX values('222222222','KAWA','東京都','千代田区','○○2-1-1','20060103',null);
commit;

SQL

--■■■max関数を使う方法■■■
update XXX a
set FLG = (select WillFLG
             from (select b.RowID as Row_ID,
                   decode(b.FFF,
                   max(b.FFF) over(partition by b.AAA,b.BBB,b.CCC,b.DDD,b.EEE),'0','1')
                   as WillFLG
                     from XXX b)
            where a.RowID = Row_ID);

--■■■in述語の引数に、複数行複数列のselect文を使う方法■■■
update XXX
set FLG = case when (aaa,bbb,ccc,ddd,eee,fff)
            in (select aaa,bbb,ccc,ddd,eee,max(fff)
                  from XXX
              group by aaa,bbb,ccc,ddd,eee) then '0' else '1' end;

--■■■Last_Value関数を使う方法■■■
update XXX
set FLG = case when RowID
                 in (select
                     Last_Value(RowID)
                     over(partition by aaa,bbb,ccc,ddd,eee
                     order by fff Rows between Unbounded Preceding and Unbounded Following)
                       from XXX) then '0' else '1' end;

解説

いずれの方法も、
非相関サブクエリを使ってます

---