Obtaining the Max Values Across Columns
Published: 4th February 2015
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:

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


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:

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


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.
Comments:
NB: Comments will only appear once they have been moderated.