10-252 10位がなかったら、最も近い順位を出力

SQLパズル

ValTable
ID  Val
--  ---
 2    1
 2    3
 2    5
 2    6
 2    7
 2    9
 2   11
 2   14
 2   16
 2   23
 2   24
 2   54
 3    6
 3    7
 3    8
 3    9
 3   23
 4    3
 4    5
 4    8
 4    9
 4    9
 4    9
 5    2
 5    7

IDごとで、Valの昇順で順位(Row_Number)をつけ、
10位のレコードを出力する。
ただし、10位のレコードがなかったら、最も近い順位を出力する。

出力結果
ID  Val
--  ---
 2   23
 3   23
 4    9
 5    7

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

データ作成スクリプト

create table ValTable(ID,Val) as
select 2, 1 from dual union all
select 2, 3 from dual union all
select 2, 5 from dual union all
select 2, 6 from dual union all
select 2, 7 from dual union all
select 2, 9 from dual union all
select 2,11 from dual union all
select 2,14 from dual union all
select 2,16 from dual union all
select 2,23 from dual union all
select 2,24 from dual union all
select 2,54 from dual union all
select 3, 6 from dual union all
select 3, 7 from dual union all
select 3, 8 from dual union all
select 3, 9 from dual union all
select 3,23 from dual union all
select 4, 3 from dual union all
select 4, 5 from dual union all
select 4, 8 from dual union all
select 4, 9 from dual union all
select 4, 9 from dual union all
select 4, 9 from dual union all
select 5, 2 from dual union all
select 5, 7 from dual;

SQL

--■■■分析関数を使う方法■■■
select ID,Val
from (select ID,Val,
      Row_Number() over(partition by ID order by Val) as RN,
      count(*) over(partition by ID) as maxRN
        from ValTable)
 where RN = Least(maxRN,10);

--■■■分析関数を使わない方法■■■
select ID,max(Val) as Val
from (select ID,Val,
      (select count(*)+1
         from ValTable b
        where b.ID = a.ID
          and b.Val < a.Val
           or b.Val = a.Val and b.RowID < a.RowID) as rn
        from ValTable a)
 where rn <= 10
group by ID;

解説

分析関数を使う方法では、
Row_Numberの最大値が、count(*)と等しくなることを使い、
インラインビューの階層を1つ減らしてます。

---