Here’s a simple and efficient way to find the maximum value in each row using SQL Server UNPIVOT
DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)
-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2
-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(
SELECT id, col FROM @t
UNPIVOT
(col FOR ListofColumns IN (col1,col2,col3))
AS unpivott) AS p
GROUP BY id
OUTPUT
If you new to UNPIVOT, read Using PIVOT and UNPIVOT
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
7 comments:
Simply brilliant. Can i find the highest and lowest value using any other approach than UNPIVOT. If you have the time, I just need this example for my knowledge
Taylor,
Try this method
select id, min(col1) as minValue, max(col1) as maxValue from
(
select id, col1 from @t
union all
select id, col2 from @t
union all
select id, col3 from @t
) as t
group by id
If you use version 2008, you can use values clause, refer point 3 in this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/08/02/values-clause-in-sql-server-2008.aspx
Thanks Madhivanan. I too had written a query yesterday in response to this comment where I used CROSS APPLY. The advantage was using this, the OP can update existing columns if there's a need.
http://www.sqlservercurry.com/2011/02/sql-server-highest-and-lowest-values-in.html
Excellent, great, useful.. Saved my 5 days effort and frustration..
Awesome thinking, efficient method.
Thanks so so so much :)
My question is to Madhivanan:
First of all thanks for the solution given above.
But adding to the above question if id column is not given, then how will we do it
SQLGeek, if you do not have id column, you can use CASE expression
select
case
when col1>col2 and col1>col3 then col1
when col2>col3 then col2
else col3
end as maximum_value
from table
Post a Comment