10-54 count関数で、order by指定

SQLパズル

テーブル
Key  ColA  ColB
---  ----  ----
  1     1     1
  2     1     1
  3     1     2
  4     2     2
  5     2     2

Keyが自分より小さくて、
ColAとColBが等しいレコードが存在するKeyを出力する

出力結果
Key
---
  2
  5

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

データ作成スクリプト

create table Table1054(
Key  number(1),
ColA number(1),
ColB number(1));

insert into Table1054 values(1,1,1);
insert into Table1054 values(2,1,1);
insert into Table1054 values(3,1,2);
insert into Table1054 values(4,2,2);
insert into Table1054 values(5,2,2);
commit;

SQL

--■■■count関数を使う方法■■■
select Key
from (select Key,
      count(*) over(partition by ColA,ColB order by Key) as CheckCount
      from Table1054)
where CheckCount > 1;

--■■■Row_Number関数を使う方法■■■
select Key
from (select Key,
      Row_Number() over(partition by ColA,ColB order by Key) as Rank
      from Table1054)
where Rank > 1;

--■■■existsを使う方法■■■
select Key
  from Table1054 a
 where exists(select 1 from Table1054 b
               where b.Key < a.Key
                 and b.ColA = a.ColA
                 and b.ColB = a.ColB);

解説

count関数を使う方法では、
windowing_clauseを省略すると、
Range between unbounded preceding and current rowになることを使ってます

---