10-212 最大値を使って、それ以下の最大値を求める

SQLパズル

MaxMinTable
ID  Val
--  ---
 1   10
 1   20
 1   30
 2   15
 2   25
 2   35
 2   45

ID=1の中での最大値と、
ID=2の中で、ID=1の最大値以下の最大値を求める。

出力結果
ID  Val
--  ---
 1   30
 2   25

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table MaxMinTable(ID,Val) as
select 1,10 from dual union
select 1,20 from dual union
select 1,30 from dual union
select 2,15 from dual union
select 2,25 from dual union
select 2,35 from dual union
select 2,45 from dual;

SQL

--■■■最後にgroup化する方法■■■
select ID,max(Val) as Val
from (select ID,Val,max(decode(ID,1,Val)) over () as MaxVal
      from MaxMinTable)
 where Val <= MaxVal
group by ID
order by ID;

--■■■最後にgroup化しない方法■■■
select MaxVal as Val1,Val2
from (select ID,Val,
      max(decode(ID,1,Val)) over () as MaxVal,
      Last_Value(case when ID = 2 then Val end ignore nulls) over(order by Val) as Val2
      from MaxMinTable)
 where ID = 1
   and Val = MaxVal;

解説

最後にgroup化する方法が分かりやすそうですね。

---