Obtaining the Max Values Across Columns
Published: Oct 30, 2016
In some code I was writing recently I needed to perform this action which, having had a quick look on the internet, seems to be a topic that annoy people as it’s not something natively built in to SQL Server and to obtain it yourself can be a pain.
I’ve seen a good few people try to achieve this in a function because this seems an easier way to produce the required result. But, as we know, functions have a lot of flaws (such as prohibiting parallelism) and therefore I wanted another approach.
What I’ve been using the method shown below. In many cases I’ve been using it to replace a maxValue function in queries because it’s fast and it also allows parallelism to be used, which has helped immensely when tuning some queries on very large data warehouse datasets.
Here’s a quick example to show the method I’ve chosen:
And I liked this because it scales well (unlike using a case statement, for example) and also works just as happily if you have multiple tables:
I’m not saying that this is the best method… I imagine there are CLR versions out there which could be faster, or even t-SQL versions… but this is definitely my favourite method at the moment because it’s easy to implement, scalable, and can cater for any number of columns without hassle.
I’ve seen a good few people try to achieve this in a function because this seems an easier way to produce the required result. But, as we know, functions have a lot of flaws (such as prohibiting parallelism) and therefore I wanted another approach.
What I’ve been using the method shown below. In many cases I’ve been using it to replace a maxValue function in queries because it’s fast and it also allows parallelism to be used, which has helped immensely when tuning some queries on very large data warehouse datasets.
Here’s a quick example to show the method I’ve chosen:
declare @table1 table
(
id int identity,
val1 int,
val2 int,
val3 int
)
insert into @table1
values(2, 4, 5), (1, 7, 4), (8, 1, 3), (6, 12, 7),
(1, 1, 1), (4, 3, 15), (9, 3, 7), (8, 8, 8)
select t.*, z.maxValue
from @table1 t
cross apply
(
select max(x) maxValue
from
(
values(val1),
(val2),
(val3)
) value(x)
) z
order by t.id
declare @table1 table
(
id int identity,
val1 int,
val2 int,
val3 int
)
insert into @table1
values(2, 4, 5), (1, 7, 4), (8, 1, 3), (6, 12, 7),
(1, 1, 1), (4, 3, 15), (9, 3, 7), (8, 8, 8)
declare @table2 table
(
id int identity,
val4 int,
val5 int
)
insert into @table2
values(11, 9), (1, 2), (17, 3), (4, 6)
select t.*, t2.val4, t2.val5, z.maxValue
from @table1 t
join @table2 t2
on t.id = t2.id
cross apply
(
select max(x) maxValue
from
(
values(t.val1),
(t.val2),
(t.val3),
(t2.val4),
(t2.val5)
) value(x)
) z
order by t.id