SQL to find max value across row and column

 

Expected Output:

 

 

drop table max_value;

create table  max_value(c1 number, c2 number, c3 number);

insert into max_value values(1,2,3);

insert into max_value values(4,5,6);

insert into max_value values(7,8,9);

commit;

Method1:

select max(c1),max(c2),max(c3) from max_value;

select greatest(max(c1),max(c2),max(c3)) from max_value;

 

(select 'C1' column_name,max(c1) val from max_value

union all

select 'C2' column_name,max(c2)  val from max_value

union all

select 'C3' column_name,max(c3) val from max_value)

 

with d as (select 'C1' column_name,max(c1) val from max_value

union all

select 'C2' column_name,max(c2)  val from max_value

union all

select 'C3' column_name,max(c3) val from max_value)

select column_name,val from d

where val=(select max(val) from d);

 

Method2:

(select 'C1' column_name,c1 val from max_value

union all

select 'C2' column_name,c2  val from max_value

union all

select 'C3' column_name,c3 val from max_value);

 

with d as (select 'C1' column_name,c1 val from max_value

union all

select 'C2' column_name,c2  val from max_value

union all

select 'C3' column_name,c3 val from max_value)

select column_name,val,max(val) over() from d;

 

with d as (select 'C1' column_name,c1 val from max_value

union all

select 'C2' column_name,c2  val from max_value

union all

select 'C3' column_name,c3 val from max_value)

select column_name,case when val = max(val) over()  then val end val from d;

 

 

with d as (select 'C1' column_name,c1 val from max_value

union all

select 'C2' column_name,c2  val from max_value

union all

select 'C3' column_name,c3 val from max_value)

select * from(select column_name,case when val = max(val) over()  then val end val from d)

where val is not null;

 

Method 3:

select * from max_value

unpivot(col_val for column_name in (c1,c2,c3));

 

(select column_name,col_val from max_value

unpivot(col_val for column_name in (c1,c2,c3)));

 

with d as (select column_name,col_val from max_value

unpivot(col_val for column_name in (c1,c2,c3)))

select column_name,col_val from d where col_val=(select max(col_val) from d);

 

Method4:

with d as (select column_name,col_val from max_value

unpivot(col_val for column_name in (c1,c2,c3)))

select * from(select column_name,case when col_val = max(col_val) over()  then col_val end val from d)

where val is not null;