6-3 項目別のTOP3を取得

SQLパズル

Tテーブル
Name  Value  ID
----  -----  --
   A     10   1
   A     20   2
   A     30   3
   A     40   4
   A     50   5
   B    100   6
   B    200   7
   B    300   8
   B    400   9

Name別の、ValueのTOP3を求める。

出力結果
Name  Value  ID  Rank
----  -----  --  ----
   A     10   1     1
   A     20   2     2
   A     30   3     3
   B    100   6     1
   B    200   7     2
   B    300   8     3

データ作成スクリプト

create table T(
Name  char(1),
Value number(3),
ID    number(1),
primary key(ID));

insert into T values ('A', 10,1);
insert into T values ('A', 20,2);
insert into T values ('A', 30,3);
insert into T values ('A', 40,4);
insert into T values ('A', 50,5);
insert into T values ('B',100,6);
insert into T values ('B',200,7);
insert into T values ('B',300,8);
insert into T values ('B',400,9);
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select Name,Value,ID,
(select count(b.ID)+1 from T b
        where b.name=a.name
          and b.Value < a.Value) as Rank
from T a
where (select count(b.ID)+1 from T b
        where b.name=a.name
          and b.Value < a.Value) <=3
order by ID;

--■■■相関サブクエリとインラインビューを使う方法■■■
select Name,Value,ID,rank
from (select Name,Value,ID,
             (select count(b.ID)+1 from T b
               where b.name=a.name
                 and b.Value < a.Value) as Rank
        from T a)
where Rank <=3
order by ID;

--■■■分析関数を使用する方法■■■
select Name,Value,ID,Rank from
    (select Name,Value,ID,
     Rank() over (partition by Name order by Value) as Rank
     from T)
where Rank <=3
order by ID;

解説

相関サブクエリを使う方法では、
同じnameで、Valueが小さいレコードの数を、
相関サブクエリで取得して、順位を取得してます。

---