Get the Maximum Value Across Columns in TSQL

There is no TSQL function for extracting maximum values across columns. However, you can use MAX() and MIN() functions to obtain the maximum or minimum value in any particular row.

The following example shows how to get max value across columns in a table:

--////////////////////////////////////////////////CREATE TABLE #tblScores (StudentID INT PRIMARY KEY,Maths INT,Physics INT,History INT,Chemistry INT)INSERT INTO #tblScores values (1,90,70,75,65)INSERT INTO #tblScores values (2,80,65,85,70)INSERT INTO #tblScores values (3,65,60,55,40)INSERT INTO #tblScores values (4,85,70,75,65)INSERT INTO #tblScores values (5,50,80,70,50)--SELECT * FROM #tblScoresSELECT StudentID, Maths, Physics, History, Chemistry ,	CASE WHEN Maths < Physics THEN	      CASE WHEN Physics < History THEN		 CASE WHEN History < Chemistry THEN			Chemistry		 ELSE			History		 END	      ELSE		 CASE WHEN Physics < Chemistry THEN			Chemistry		 ELSE			Physics		 END	      END	ELSE	      CASE WHEN Maths < History THEN		 CASE WHEN History < Chemistry THEN			Chemistry		 ELSE			History		 END	 ELSE		 CASE WHEN Maths < Chemistry THEN			Chemistry		 ELSE			Maths		 END	      END	END as AllMAXFROM #tblScores--///////////////////////////////////////////////
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: