Thursday, October 11, 2012

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

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...

Contributors