Compare a Row to the MAX of Multiple Columns and Multiple Rows: The Easy Way


Alright.  MAX on rows *and* columns.  This was a bit of a mind bender.  A fellow SQL Server Developer was wondering if it was possible to find the MAX and MIN values of multiple rows and columns and then compare it to a value on a single row.  Of course this could be done by creating a temp table with the aggregated data and then joining the aggregate temp table back to the original values but something told me there could be an easier way.  First things first, let’s create a little temp table to work with:

IF OBJECT_ID('tempdb..#table') IS NOT NULL
 BEGIN
 DROP TABLE #table
 END

CREATE TABLE #table ( colA INT, colB INT )

INSERT  #table
 ( colA, colB )
 VALUES
 ( -10, 0 ),
 ( 99, 50 ),
 ( NULL, 25 ),
 ( -99, 222 ),
 ( -99, 222 ),
 ( -555, NULL )

Whoa! Did you see what I did there!?! A quick look at my insert statement will tell you this post is SQL Server 2008 compliant (and greater).  It will not work with SQL Server 2005 or older.  That holds true for the rest of the solution.  Not just the insert statement.  So, back to our story.  My first instinct was that a CASE statement combined with the OVER clause would do the trick.  In some cases this would definitely be possible.  An example of the MAX plus the OVER would be something like this:

SELECT  t.colA
 , t.colB
 , MAX(colA) OVER ( PARTITION BY 1 ) AS MaxColA
 , MAX(colB) OVER ( PARTITION BY 1 ) AS MaxColB
 FROM    #table AS t

And the results would be:

colA        colB        MaxColA     MaxColB
 ----------- ----------- ----------- -----------
 -10         0           99          222
 99          50          99          222
 NULL        25          99          222
 -99         222         99          222
 -99         222         99          222
 -555        NULL        99          222

If you nested the case inside the MAX you might have something.  Unfortunately, the existence of null values eliminated the CASE option since the nulls wouldn’t be valid for comparison across columns (unless you could use an ISNULL and come up with a value which could never appear in the actual data, risky business in my opinion).  Stumbling around in Google I came across a stack overflow post which explained a slick way to compare multiple columns within a row, it handled the nulls fine but it did not handle the case of multiple columns across multiple rows.  The solution basically looked like this and finds the maximum value from multiple columns within a row:

SELECT  t.colA
 , t.colB
 , ( SELECT    MAX(RowVals) AS MaxRowVals
     FROM      ( VALUES ( colA), ( colB) ) AS t1 ( RowVals )
    ) AS RowMax
 FROM    #table AS t

The results for this one would be:

colA        colB        RowMax
 ----------- ----------- -----------
 -10         0           0
 99          50          99
 NULL        25          25
 -99         222         222
 -99         222         222
 -555        NULL        -555

This is basically where this solution becomes a SQL Server 2008, and I’m assuming SQL Server 2012, only solution (to be honest I was totally unaware of the new VALUES functionality until I came across the StackOverflow piece.  There’s a great write-up about the enhancements to the VALUES clause at BeyondRelational.com.  On a side note: is there a limit to how many sentences you can pack inside a set of parentheses?  I suspect the official answer is zero).

With a little experimentation I was able to come up with this wizardly little piece of code which combines the OVER window function with some of the new VALUES functions in SQL Server 2008 (.  So, check out the code below.  I think it’s a pretty wizard way to return the greatest (or smallest) value for a group of columns across multiple rows.

SELECT  t.colA
 , t.colB
 , MAX(( SELECT    MAX(RowVals) AS MaxRowVals
         FROM      ( VALUES ( colA), ( colB) ) AS t1 ( RowVals )
       )) OVER ( PARTITION BY 1 ) AS MaxColRowVal
 FROM    #table AS t

And… Wait for it… The results!

colA        colB        MaxColRowVal
 ----------- ----------- ------------
 -10         0           222
 99          50          222
 NULL        25          222
 -99         222         222
 -99         222         222
 -555        NULL        222

This is a pretty finicky piece of code.  Make sure you alias the MAX on the inner select, “MaxRowVals” in the example.  Even though this alias will not be displayed anywhere Microsoft SQL Server requires it.  Otherwise you end up with a syntax error.

MAX across rows AND columns! Aw Yeah!

Leave a Reply

Your email address will not be published.