February 20, 2011

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

SQL Highest Lowest

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

SQL Highest Lowest


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "SQL Server: Highest and Lowest Values in a Row"
  1. sugat misra said...
    January 30, 2013 at 12:49 PM

    Hi Sir,
    Your site is really excellent.It helped me a lot.

    Can you please explain me the above logic?

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions