10-289 An alternative to DISTINCT

SQLパズル

EmuDistinctテーブル
Col1  Col2
----  ----
   1  A
   1  A
   2  A
   2  A
   2  B
   3  C
   3  C
   4  C
   4  C
   4  D

重複行を排除した結果において、
Col1ごとで、Col2が2件以上となる、
行を出力する。

出力結果
Col1  Col2
----  ----
   2  A
   2  B
   4  C
   4  D

データ作成スクリプト

create table EmuDistinct(Col1,Col2) as
select 1,'A' from dual union all
select 1,'A' from dual union all
select 2,'A' from dual union all
select 2,'A' from dual union all
select 2,'B' from dual union all
select 3,'C' from dual union all
select 3,'C' from dual union all
select 4,'C' from dual union all
select 4,'C' from dual union all
select 4,'D' from dual;

SQL

--■■■group byで重複消しする方法■■■
select Col1,Col2
from (select Col1,Col2,
      count(*) over(partition by Col1) as cnt
        from EmuDistinct
      group by Col1,Col2)
where cnt > 1;

--■■■Row_Number関数で重複消しする方法■■■
select Col1,Col2
from (select Col1,Col2,
      Row_Number() over(partition by Col1,Col2 order by 1) as rn,
      count(distinct Col2) over(partition by Col1) as disCnt
        from EmuDistinct)
 where rn= 1
   and disCnt > 1;

--■■■distinctで重複消しする方法■■■
select distinct Col1,Col2
from (select Col1,Col2,
      count(distinct Col2) over(partition by Col1) as disCnt
        from EmuDistinct)
where disCnt > 1
order by Col1,Col2;

解説

Oracle ACE DirectorのEddie AwadさんのAn alternative to DISTINCT
を意識してみました :-)

重複消しが関連するSQLでは、
・distinct
・group by
・Row_Number関数
を使い分けるといいようです。

10-190 distinctオプションとorder by指定の分析関数を代用
10-274 distinct後のorder by

---