Click here to Skip to main content
14,698,679 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
C1          C2          c3          MaxVal
----------- ----------- ----------- -----------
100         1000        300          200
200         456         780          1000
                                     780


What I have tried:

<pre>I WANT MAX VALUE FOR COLUMN
Posted
Updated 10-Sep-19 23:37pm
Comments
Mohibur Rashid 10-Sep-19 17:44pm
   
So, you didn't even tried to ask the question properly.

Type "SQL MAX" into Google and read what comes up.

This is EASILY answered just by reading the documentation on the MAX function.
   
Depending on what you're trying to achieve...

1) If you want to get max of all columns in all rows:
SELECT MAX(C123) AS MaxValue
FROM (
	SELECT C1 AS C123
	FROM @tmp
	UNION ALL
	SELECT C2 AS C123
	FROM @tmp
	UNION ALL
	SELECT C3 AS C123
	FROM @tmp
	) AS T

--returns 1000


2) If you want to get max value of each column (in a single row):
SELECT MAX(C1) AS C1Max, MAX(C2) AS C2Max, MAX(C3) AS C3Max
FROM @tmp

-- returns:
--200	1000	780


3) If you want to get max value of each column (in rows with column names):
SELECT ColName, MAX(ColValue) AS MaxValueOfCol
FROM (
	SELECT ColName, ColValue
	FROM (
		SELECT C1, C2, C3
		FROM @tmp
	) AS PVT
	UNPIVOT(ColValue FOR ColName IN([C1], [C2], [C3])) AS UNPVT
	) AS T
GROUP BY ColName
--returns:
--C1	200
--C2	1000
--C3	780


Note: replace @tmp with the name of table in your database.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900