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