10-211 distinct指定とorder by

SQLパズル

SortWithDistinctテーブル
Val1  Val2  sortKey  SubSortKey
----  ----  -------  ----------
   1     3       10           1
   1     3       10           1
   1     3       10           1
   2     4       30           2
   2     4       30           2
   3     5       20           1
   3     5       20           1
   4     6       10           3
   5     5       10           2
   5     5       10           2
   9     9       10           4
   6     4       20           2
   6     4       20           2
   7     3       30           1
   7     3       30           1

Val1とVal2の組み合わせで重複を排除して
sortKeyの昇順,SubSortKeyの昇順
で出力する。

出力結果
Val1  Val2
----  ----
   1     3
   5     5
   4     6
   9     9
   3     5
   6     4
   7     3
   2     4

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

データ作成スクリプト

create table SortWithDistinct(Val1,Val2,sortKey,SubSortKey) as
select 1,3,10,1 from dual union all
select 1,3,10,1 from dual union all
select 1,3,10,1 from dual union all
select 2,4,30,2 from dual union all
select 2,4,30,2 from dual union all
select 3,5,20,1 from dual union all
select 3,5,20,1 from dual union all
select 4,6,10,3 from dual union all
select 5,5,10,2 from dual union all
select 5,5,10,2 from dual union all
select 9,9,10,4 from dual union all
select 6,4,20,2 from dual union all
select 6,4,20,2 from dual union all
select 7,3,30,1 from dual union all
select 7,3,30,1 from dual;

SQL

--■■■グループ化する方法■■■
select Val1,Val2
  from SortWithDistinct
group by Val1,Val2
order by max(sortKey),max(SubSortKey);

--■■■インラインビューを使う方法■■■
select Val1,Val2
from (select distinct Val1,Val2,sortKey,SubSortKey
        from SortWithDistinct)
order by sortKey,SubSortKey;

--■■■dense_Rank関数を使う方法■■■
select Val1,Val2
from (select distinct Val1,Val2,
      dense_Rank() over(order by sortKey,SubSortKey) as willSortKey
        from SortWithDistinct)
order by willSortKey;

解説

下記は、文法エラーとなります。
select distinct Val1,Val2
  from SortWithDistinct
order by sortKey,SubSortKey;
ORA-01791: not a SELECTed expression

なお、distinct指定による、重複排除は分析関数の後に行われます。

SQL> select distinct ColA,ColB,Row_Number() over(order by 1) as Rank
  2  from (select 1 as ColA,1 as ColB from dual
  3  union all select 1,1 from dual
  4  union all select 1,1 from dual
  5  union all select 1,1 from dual
  6  union all select 2,2 from dual
  7  union all select 2,2 from dual
  8  union all select 2,2 from dual)
  9  order by 1,2,3;

ColA  ColB  Rank
----  ----  ----
   1     1     1
   1     1     2
   1     1     3
   1     1     4
   2     2     5
   2     2     6
   2     2     7

1-6 select文の評価順序

---