Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: TSQL
Expertise: Intermediate
Jul 28, 2003

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 #tblScores

SELECT 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 AllMAX
FROM #tblScores
--///////////////////////////////////////////////
Harinatha Reddy
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap