10-179 同一行で最大のデータを求める

SQLパズル

TestTable
ID  Num1  Num2  Num3  Num4
--  ----  ----  ----  ----
 1  null  null  null     1
 2  null  null     2  null
 3  null     3  null  null
 4     4  null  null  null
 5     5     6     7     8
 6     9    10    11  null
 7  null  null    12    13
 8    14    15  null    17
 9  null    18    19  null

同一行でのNum1,Num2,Num3,Num4の最大値を求める。
ただし、nullは除外して考える。

出力結果
ID  Num1  Num2  Num3  Num4  maxNum
--  ----  ----  ----  ----  ------
 1  null  null  null     1       1
 2  null  null     2  null       2
 3  null     3  null  null       3
 4     4  null  null  null       4
 5     5     6     7     8       8
 6     9    10    11  null      11
 7  null  null    12    13      13
 8    14    15  null    17      17
 9  null    18    19  null      19

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

データ作成スクリプト

create table TestTable(
ID   number(1),
Num1 number(2),
Num2 number(2),
Num3 number(2),
Num4 number(2));

insert into TestTable values(1,null,null,null,   1);
insert into TestTable values(2,null,null,   2,null);
insert into TestTable values(3,null,   3,null,null);
insert into TestTable values(4,   4,null,null,null);
insert into TestTable values(5,   5,   6,   7,   8);
insert into TestTable values(6,   9,  10,  11,null);
insert into TestTable values(7,null,null,  12,  13);
insert into TestTable values(8,  14,  15,null,  17);
insert into TestTable values(9,null,  18,  19,null);
commit;

SQL

select ID,Num1,Num2,Num3,Num4,
greatest(coalesce(Num1,Num2,Num3,Num4),
         coalesce(Num2,Num1,Num3,Num4),
         coalesce(Num3,Num1,Num2,Num4),
         coalesce(Num4,Num1,Num2,Num3)) as maxNum
from TestTable
order by ID;

解説

greatest関数やLeast関数は、引数にnullがあると、nullを返すので、
coalesce関数でnullを引数にするのを防いでます。

---