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

SQLパズル

MyTable2
 A   B   C   D   E
--  --  --  --  --
15  20  10  18  99
16  17  13  20  11
18  23  40  29  33
30  31  32  33  34
44  43  42  41  40
54  51  52  53  50
63  64  62  61  60
74  73  72  70  71

同じ行で、列の昇順にソートする。

出力結果
 A   B   C   D   E
--  --  --  --  --
10  15  18  20  99
11  13  16  17  20
18  23  29  33  40
30  31  32  33  34
40  41  42  43  44
50  51  52  53  54
60  61  62  63  64
70  71  72  73  74

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

データ作成スクリプト

create table MyTable2(A,B,C,D,E) as
select 15,20,10,18,99 from dual union
select 16,17,13,20,11 from dual union
select 18,23,40,29,33 from dual union
select 30,31,32,33,34 from dual union
select 44,43,42,41,40 from dual union
select 54,51,52,53,50 from dual union
select 63,64,62,61,60 from dual union
select 74,73,72,70,71 from dual;

SQL

select a,b,c,d,e
from (select least(a,b) as a,greatest(a,b) as b,least(c,d) as c,greatest(c,d) as d,e
from (select a,least(b,c) as b,greatest(b,c) as c,least(d,e) as d,greatest(d,e) as e
from (select least(a,b) as a,greatest(a,b) as b,least(c,d) as c,greatest(c,d) as d,e
from (select a,least(b,c) as b,greatest(b,c) as c,least(d,e) as d,greatest(d,e) as e
from (select least(a,b) as a,greatest(a,b) as b,least(c,d) as c,greatest(c,d) as d,e
from (select a,least(b,c) as b,greatest(b,c) as c,least(d,e) as d,greatest(d,e) as e
        from MyTable2))))));

解説

バブルソートを使って、ソートしてます。

バブルソート - Wikipedia
＠IT自分戦略研究所  バブルソート

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

---