T-SQL Query | [Finding Maximum Value from multiple columns] – Write a query which will find maximum value from multiple columns of the table

Sample Input

 Year1 Max1 Max2 Max3 2001 10 101 87 2002 103 19 88 2003 21 23 89 2004 27 28 91

Expected Output

 Year1 MaxValue 2001 101 2002 103 2003 89 2004 91

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Send your solution to pawankkmr@gmail.com
• Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` --Create table CREATE TABLE TestMax ( Year1 INT ,Max1 INT ,Max2 INT ,Max3 INT ) GO --Insert data INSERT INTO TestMax VALUES (2001,10,101,87) ,(2002,103,19,88) ,(2003,21,23,89) ,(2004,27,28,91) --Select data Select Year1,Max1,Max2,Max3 FROM TestMax ```

Update May 14 | Solution

 ``` -- --------------------------------------- --Sol 1 | Pawan Kumar Khowal --------------------------------------- SELECT Year1, ( SELECT Max(v) FROM ( VALUES (max1) , (max2), (max3) ) as value(v) ) as mmax FROM TestMax --------------------------------------- --Sol 2 | Pawan Kumar Khowal --------------------------------------- SELECT Year1, MAX(maxy) maxyy FROM ( SELECT Year1 , Max1 maxy FROM TestMax UNION ALL SELECT Year1 , Max2 FROM TestMax UNION ALl SELECT Year1 , Max3 FROM TestMax ) a GROUP BY YEAR1 --------------------------------------- --Sol 3 | Pawan Kumar Khowal --------------------------------------- SELECT Year1, CASE WHEN Max1 > Max2 AND MAX1 > Max3 THEN MAX1 WHEN Max2 > Max1 AND MAX2 > Max3 THEN MAX2 WHEN Max3 > Max2 AND MAX3 > Max1 THEN MAX3 END AS Maxx FROM TestMax -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com