10-28 指定列が全て最大値もしくは最小値の行を取得

SQLパズル

table1
field1  field2  field3  field4  field5
------  ------  ------  ------  ------
   100     100     100      10      20
    50      50      50      20      30
     0     100     100      40      50
     0       0       0      60      70

table1のfield1,field2,field3が全て最大値もしくは最小値
のレコードを出力する。

出力結果
field1  field2  field3  field4  field5
------  ------  ------  ------  ------
   100     100     100      10      20
     0       0       0      60      70

データ作成スクリプト

create table table1(
field1 number(3),
field2 number(3),
field3 number(3),
field4 number(3),
field5 number(3));

insert into table1 values(100,100,100,10,20);
insert into table1 values( 50, 50, 50,20,30);
insert into table1 values(  0,100,100,40,50);
insert into table1 values(  0,  0,  0,60,70);
commit;

SQL

--■■■unionを使用する方法■■■
select field1,field2,field3,field4,field5 from table1
 where (field1, field2, field3) in
(select min(field1),min(field2),min(field3) from table1
 union all
 select max(field1),max(field2),max(field3) from table1)
order by field1, field2, field3;

--■■■existsとhavingを使用する方法■■■
select field1,field2,field3,field4,field5
from table1 a
where exists
(select 1 from table1 b
 having (a.field1,a.field2,a.field3)
     in ((max(b.field1),max(b.field2),max(b.field3)),(min(b.field1),min(b.field2),min(b.field3))));

--■■■インラインビューで最大値もしくは最小値を取得する方法■■■
select field1,field2,field3,field4,field5
from table1,(select max(field1) as max1,
                    max(field2) as max2,
                    max(field3) as max3,
                    min(field1) as min1,
                    min(field2) as min2,
                    min(field3) as min3
             from table1)
where (field1,field2,field3) in ((max1,max2,max3),(min1,min2,min3));

--■■■インラインビューでRank関数を使う方法1■■■
select field1,field2,field3,field4,field5
from (select field1,field2,field3,field4,field5,
      rank() over (order by field1 desc) as desc1,
      rank() over (order by field2 desc) as desc2,
      rank() over (order by field3 desc) as desc3,
      rank() over (order by field1) as asc1,
      rank() over (order by field2) as asc2,
      rank() over (order by field3) as asc3
      from table1)
where 1 = all(desc1,desc2,desc3)
   or 1 = all(asc1,asc2,asc3)
order by field1, field2, field3;

--■■■インラインビューでRank関数を使う方法2■■■
select field1,field2,field3,field4,field5
from (select field1,field2,field3,field4,field5,
      rank() over (order by field1 desc) as desc1,
      rank() over (order by field2 desc) as desc2,
      rank() over (order by field3 desc) as desc3,
      rank() over (order by field1) as asc1,
      rank() over (order by field2) as asc2,
      rank() over (order by field3) as asc3
      from table1)
where 1 in(greatest(Desc1,Desc2,Desc3),greatest(Asc1,Asc2,Asc3))
order by field1, field2, field3;

解説

field1,field2,field3の最大値と
field1,field2,field3の最小値を取得して、
少なくとも一つと一致するかチェックしてます。

---