Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.