SQL Server: Highest and Lowest Values in a Row

Some time back, I had written a query to find Find Maximum Value in each Row – SQL Server where I used UNPIVOT to find the highest value in a row or across multiple columns. A SQLServerCurry.com reader D. Taylor wrote back asking if the same example could be written without using an UNPIVOT operator, to calculate both the highest and lowest values in a row. Well here’s another way to do it.

First create a sample table with some values

Now write the following query to use CROSS APPLY and get the highest and lowest value in a row

SELECT t.id, tt.maxValue, tt.minValue
FROM @t as t
CROSS APPLY
(
SELECT
MAX(col) as maxValue, MIN(col) as minValue
FROM
(
SELECT col1 UNION ALL
SELECT col2 UNION ALL
SELECT col3
) as temp(col)
) as tt

If you are wondering why did I use a CROSS APPLY instead of a simple correlated sub-query, then the reason is that I can work with multiple rows here. Moreover CROSS APPLY can return multiple columns too (like a derived table). At the end, we are referencing these values in our outer SELECT statement and the output is as shown below:

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook