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 Query select iid, (select MAX(myval) from (values (f_sal),(s_sal)) as all_val(myval)) from test_max Output ** 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 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>