3-23 大小関係を出力

SQLパズル

ValueListテーブル
ID  Value
--  -----
 A    100
 B     90
 C     80
 D     75
 E     75
 F     75
 G     70
 H     70
 I     60

Valueの大小関係を示す、
不等式と等式を出力する

出力結果
大小関係
-----------------
A>B>C>D=E=F>G=H>I

データ作成スクリプト

create table ValueList(
ID    char(1),
Value number(3));

insert into ValueList values('A',100);
insert into ValueList values('B', 90);
insert into ValueList values('C', 80);
insert into ValueList values('D', 75);
insert into ValueList values('E', 75);
insert into ValueList values('F', 75);
insert into ValueList values('G', 70);
insert into ValueList values('H', 70);
insert into ValueList values('I', 60);
commit;

SQL

col 大小関係 for a20

--■■■case式を使う方法■■■
select max(substr(replace(sys_connect_by_path(
case when prior Value = Value then '='
     else '>' end || ID,','),','),2)) as 大小関係
from (select ID,Value,
      Row_Number() over(order by Value desc,ID) as RowNumber
      from ValueList)
start with RowNumber = 1
connect by prior RowNumber = RowNumber-1;

--■■■decode関数を使う方法■■■
select max(substr(replace(sys_connect_by_path(
decode(prior Value,Value,'=','>') || ID,','),','),2)) as 大小関係
from (select ID,Value,
      Row_Number() over(order by Value desc,ID) as RowNumber
      from ValueList)
start with RowNumber = 1
connect by prior RowNumber = RowNumber-1;

解説

prior ValueとValueを比較して、
大小関係を取得してます

---