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:

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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron