{"id":260,"date":"2012-05-24T12:13:42","date_gmt":"2012-05-24T19:13:42","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=260"},"modified":"2012-05-25T08:58:04","modified_gmt":"2012-05-25T15:58:04","slug":"compare-a-row-to-the-max-of-multiple-columns-and-multiple-rows-the-easy-way","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/05\/compare-a-row-to-the-max-of-multiple-columns-and-multiple-rows-the-easy-way\/","title":{"rendered":"Compare a Row to the MAX of Multiple Columns and Multiple Rows: The Easy Way"},"content":{"rendered":"<p>Alright.\u00a0 MAX on rows *and* columns.\u00a0 This was a bit of a mind bender.\u00a0 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.\u00a0 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.<!--more-->\u00a0 First things first, let\u2019s create a little temp table to work with:<\/p>\n<pre>IF OBJECT_ID('tempdb..#table') IS NOT NULL\r\n BEGIN\r\n DROP TABLE #table\r\n END\r\n\r\nCREATE TABLE #table ( colA INT, colB INT )\r\n\r\nINSERT\u00a0 #table\r\n ( colA, colB )\r\n VALUES\r\n ( -10, 0 ),\r\n ( 99, 50 ),\r\n ( NULL, 25 ),\r\n ( -99, 222 ),\r\n ( -99, 222 ),\r\n ( -555, NULL )<\/pre>\n<p>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).\u00a0 It will not work with SQL Server 2005 or older.\u00a0 That holds true for the rest of the solution.\u00a0 Not just the insert statement.\u00a0 So, back to our story.\u00a0 My first instinct was that a CASE statement combined with the OVER clause would do the trick.\u00a0 In some cases this would definitely be possible.\u00a0 An example of the MAX plus the OVER would be something like this:<\/p>\n<pre>SELECT\u00a0 t.colA\r\n , t.colB\r\n , MAX(colA) OVER ( PARTITION BY 1 ) AS MaxColA\r\n , MAX(colB) OVER ( PARTITION BY 1 ) AS MaxColB\r\n FROM\u00a0\u00a0\u00a0 #table AS t<\/pre>\n<p>And the results would be:<\/p>\n<pre>colA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 colB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MaxColA\u00a0\u00a0\u00a0\u00a0 MaxColB\r\n ----------- ----------- ----------- -----------\r\n -10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 50\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -555\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222<\/pre>\n<p>If you nested the case inside the MAX you might have something.\u00a0 Unfortunately, the existence of null values eliminated the CASE option since the nulls wouldn\u2019t 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).\u00a0 Stumbling around in Google I came across a stack overflow post which explained<a title=\"SQL MAX of multiple columns\" href=\"http:\/\/stackoverflow.com\/questions\/71022\/sql-max-of-multiple-columns\"> a slick way to compare multiple columns within a row<\/a>, it handled the nulls fine but it did not handle the case of multiple columns across multiple rows.\u00a0 The solution basically looked like this and finds the maximum value from multiple columns within a row:<\/p>\n<pre>SELECT\u00a0 t.colA\r\n , t.colB\r\n , ( SELECT\u00a0\u00a0\u00a0 MAX(RowVals) AS MaxRowVals\r\n     FROM\u00a0\u00a0\u00a0\u00a0\u00a0 ( VALUES ( colA), ( colB) ) AS t1 ( RowVals )\r\n    ) AS RowMax\r\n FROM\u00a0\u00a0\u00a0 #table AS t<\/pre>\n<p>The results for this one would be:<\/p>\n<pre>colA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 colB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RowMax\r\n ----------- ----------- -----------\r\n -10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 50\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\r\n NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\r\n -99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -555\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -555<\/pre>\n<p>This is basically where this solution becomes a SQL Server 2008, and I\u2019m 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.\u00a0 There\u2019s a great write-up about the <a title=\"Enhancements to VALUES clause\" href=\"http:\/\/beyondrelational.com\/modules\/2\/blogs\/70\/posts\/10905\/interesting-enhancements-to-the-values-clause-in-sql-server-2008.aspx\">enhancements to the VALUES clause<\/a> at BeyondRelational.com.\u00a0 On a side note: is there a limit to how many sentences you can pack inside a set of parentheses?\u00a0 I suspect the official answer is zero).<\/p>\n<p>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 (.\u00a0 So, check out the code below.\u00a0 I think it\u2019s a pretty wizard way to return the greatest (or smallest) value for a group of columns across multiple rows.<\/p>\n<pre>SELECT\u00a0 t.colA\r\n , t.colB\r\n , MAX(( SELECT\u00a0\u00a0\u00a0 MAX(RowVals) AS MaxRowVals\r\n         FROM\u00a0\u00a0\u00a0\u00a0\u00a0 ( VALUES ( colA), ( colB) ) AS t1 ( RowVals )\r\n       )) OVER ( PARTITION BY 1 ) AS MaxColRowVal\r\n FROM\u00a0\u00a0\u00a0 #table AS t<\/pre>\n<p>And\u2026 Wait for it\u2026 The results!<\/p>\n<pre>colA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 colB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MaxColRowVal\r\n ----------- ----------- ------------\r\n -10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 50\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222\r\n -555\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 222<\/pre>\n<p>This is a pretty finicky piece of code.\u00a0 Make sure you alias the MAX on the inner select, \u201cMaxRowVals\u201d in the example.\u00a0 Even though this alias will not be displayed anywhere Microsoft SQL Server requires it.\u00a0 Otherwise you end up with a syntax error.<\/p>\n<figure id=\"attachment_268\" aria-describedby=\"caption-attachment-268\" style=\"width: 450px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/05\/max-value-across-rows-and-columns.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-268\" title=\"max value across rows and columns\" src=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/05\/max-value-across-rows-and-columns.jpg\" alt=\"\" width=\"450\" height=\"300\" srcset=\"http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/05\/max-value-across-rows-and-columns.jpg 450w, http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/05\/max-value-across-rows-and-columns-300x200.jpg 300w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><\/a><figcaption id=\"caption-attachment-268\" class=\"wp-caption-text\">MAX across rows AND columns! Aw Yeah!<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Alright.\u00a0 MAX on rows *and* columns.\u00a0 This was a bit of a mind bender.\u00a0 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.\u00a0 Of course this could be done by [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,3,57],"tags":[10,9,11,173,5],"class_list":["post-260","post","type-post","status-publish","format-standard","hentry","category-microsoft-sql-server","category-scripts","category-t-sql","tag-ms-sql","tag-sql-server","tag-sql-server-2008","tag-t-sql","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=260"}],"version-history":[{"count":11,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/260\/revisions"}],"predecessor-version":[{"id":274,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/260\/revisions\/274"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=260"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}