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
MAX(col) as maxValue, MIN(col) as minValue
SELECT col1 UNION ALL
SELECT col2 UNION ALL
) 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:
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|