Daily Archives: March 19, 2015

T-SQL Query | Finding Maximum Value from multiple columns Puzzle

19 Thursday Mar 2015

Posted by in T SQL Puzzles

≈ 4 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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