model句13 列値を昇順にソート

SQLパズル

sortTable
ID  Val1  Val2  Val3  Val4  Val5
--  ----  ----  ----  ----  ----
10     1     2     3     4     5
20     5     4     3     2     1
30     1     1     1     1     5
40     1     5     5     5     5
50     1     2     3     2     1
60     1     2     3     1     5

行ごとに、
Val1,Val2,Val3,Val4,Val5
を昇順にソートする。

出力結果
ID  Val1  Val2  Val3  Val4  Val5
--  ----  ----  ----  ----  ----
10     1     2     3     4     5
20     1     2     3     4     5
30     1     1     1     1     5
40     1     5     5     5     5
50     1     1     2     2     3
60     1     1     2     3     5

データ作成スクリプト

create table sortTable(ID,Val1,Val2,Val3,Val4,Val5) as
select 10,1,2,3,4,5 from dual union all
select 20,5,4,3,2,1 from dual union all
select 30,1,1,1,1,5 from dual union all
select 40,1,5,5,5,5 from dual union all
select 50,1,2,3,2,1 from dual union all
select 60,1,2,3,1,5 from dual;

SQL

select ID,Val1,Val2,Val3,Val4,Val5
  from sortTable
 model
 dimension by (ID)
 measures(Val1,Val2,Val3,Val4,Val5,0 as GWork,0 as LWork)
 rules ITERATE(4)
 (GWork[any] =    Least(Val1[CV()],Val2[CV()]),
  LWork[any] = greatest(Val1[CV()],Val2[CV()]),
  Val1[any]  = GWork[CV()],
  Val2[any]  = LWork[CV()],
  GWork[any] =    Least(Val2[CV()],Val3[CV()]),
  LWork[any] = greatest(Val2[CV()],Val3[CV()]),
  Val2[any]  = GWork[CV()],
  Val3[any]  = LWork[CV()],
  GWork[any] =    Least(Val3[CV()],Val4[CV()]),
  LWork[any] = greatest(Val3[CV()],Val4[CV()]),
  Val3[any]  = GWork[CV()],
  Val4[any]  = LWork[CV()],
  GWork[any] =    Least(Val4[CV()],Val5[CV()]),
  LWork[any] = greatest(Val4[CV()],Val5[CV()]),
  Val4[any]  = GWork[CV()],
  Val5[any]  = LWork[CV()]);

解説

ワーク変数を用意して、3角交換を行ってます。
ソートアルゴリズムとしては、バブルソートを使ってます。

10-222 バブルソートで列をソート

---