3-5 順位(DENSE)が指定した範囲のデータを取得

SQLパズル

TestResultテーブルのPointの順位(DENSE)が、
16位から20位のデータを出力する。

出力結果
Point  Rank
-----  ----
   53    16
   52    17
   51    18
   43    19
   42    20

データ作成スクリプト

create table TestResult(Point number(2));

insert into TestResult(Point)
      select 99 from dual union select 98 from dual
union select 97 from dual union select 89 from dual
union select 88 from dual union select 87 from dual
union select 79 from dual union select 78 from dual
union select 77 from dual union select 69 from dual
union select 68 from dual union select 67 from dual
union select 59 from dual union select 58 from dual
union select 57 from dual union select 53 from dual
union select 52 from dual union select 51 from dual
union select 43 from dual union select 42 from dual
union select 41 from dual union select 33 from dual
union select 32 from dual union select 31 from dual
union select 23 from dual union select 22 from dual
union select 21 from dual union select 13 from dual
union select 12 from dual union select 11 from dual;
commit;

SQL

--■■■インラインビューでソートする方法(Pointに重複データが存在しない場合のみ使用可)■■■
select Point,Rank from
    (select Point,RowNum as Rank from
        (select Point from TestResult order by Point desc))
where Rank between 16 and 20;

--■■■相関サブクエリを使う方法■■■
select Point,
(select count(distinct b.Point) from TestResult b
        where b.Point > a.Point)+1 as Rank
from TestResult a
where (select count(distinct b.Point) from TestResult b
        where b.Point > a.Point)+1 between 16 and 20
order by Rank;

--■■■分析関数のdense_rankを使用する方法■■■
select Point,Rank from
    (select point,
     dense_rank() over (order by Point desc) as Rank
     from TestResult)
where Rank between 16 and 20;

解説

Pointに重複データが存在しない場合は、
RowNumと順位を一致させることが可能なので
インラインビューにorder byでソートをかけて、
RowNumにbetweenで範囲指定をして、順位が指定した範囲のデータを取得できます。

相関サブクエリで順位を求める方法では、
Count関数にdistinctを指定して、
DENSEな順位を取得してます。

分析関数を使用する方法は、
Pointに重複データが存在したとしても使用可能で、可読性も高いです。

---

分析関数の資料(SQLクリニック)
分析関数の資料(OTN)

---