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.
