July 07, 2010

Find Maximum Value in each Row – SQL Server

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)

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

SELECT id, MAX(col) AS maxValue
SELECT id, col FROM @t
(col FOR ListofColumns IN (col1,col2,col3))
AS unpivott) AS p



If you new to UNPIVOT, read Using PIVOT and UNPIVOT

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



7 Responses to "Find Maximum Value in each Row – SQL Server"
  1. D. Taylor said...
    February 19, 2011 at 12:00 AM

    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

  2. Madhivanan said...
    February 20, 2011 at 11:35 PM


    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

  3. Suprotim Agarwal said...
    February 21, 2011 at 2:34 AM

    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.


  4. Gopal said...
    February 26, 2013 at 3:39 AM

    Excellent, great, useful.. Saved my 5 days effort and frustration..

    Awesome thinking, efficient method.

    Thanks so so so much :)

  5. SQLGeek said...
    October 27, 2015 at 10:34 PM

    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

  6. Unknown said...
    October 27, 2015 at 10:59 PM
    This comment has been removed by the author.
  7. madhivanan said...
    October 27, 2015 at 11:07 PM

    SQLGeek, if you do not have id column, you can use CASE expression

    when col1>col2 and col1>col3 then col1
    when col2>col3 then col2
    else col3
    end as maximum_value
    from table


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