max function from multiple columns

SQL Server max function from multiple columns

 

MAX function returns the maximum values across the rows in a column , but to retrieve the MAX values across columns then there is no function available like COALESCE which gives the first non-null value across columns.

There are many other techniques available to get the Max values , but I found this one very effective in terms of simplicity and performance.

To simulate this , create a table with some test data using the below query.

create table test_max (iid int ,f_sal int , s_sal int)

insert into test_max select 1,2,null
insert into test_max select 2,30,40
insert into test_max select 3,50,30


select * from test_max

Table Structure

image

Query

select iid,
(select MAX(myval) from (values (f_sal),(s_sal)) as all_val(myval))
from test_max

Output

image

** same thing we can achieve through union as below , but the advantage of using the Query-1 is you can make the statement simpler even if the no.of columns are more than 2

select iid , MAX(sal) from
(
select iid , f_sal as sal from test_max
union
select iid , s_sal from test_max
)x
group by iid

output

image

but if you see the performance between both the queries , query 1 is taking 16% where query-2 is taking 84%

execute both the sql statements in SQL-1 and SQL-2 and Press Ctrl+L or click on [Display Estimated Execution Plan]

--Query 1
select iid,
(select MAX(myval) from (values (f_sal),(s_sal)) as all_val(myval))
from test_max

--Query 2
select iid , MAX(sal) from
(
select iid , f_sal as sal from test_max
union
select iid , s_sal from test_max
)x
group by iid

<click on the below Image to open in a new window>

image

 
Post a Comment