Friday, October 14, 2016

MAX Value From Multiple Columns



CREATE TABLE #MAXCOLUMNVALUES
(ID INT IDENTITY(1,1)
,INFYSalary INT, WIPROSalary INT, SYNESalary INT, TCSSalary INT
)


INSERT INTO #MAXCOLUMNVALUES(InfySalary,WiproSalary,SyneSalary,TCSSalary) VALUES
(10000,20000,33000,25000),
(20000,55000,35000,45000),
(40000,24000,66000,50000),
(50000,51000,53000,77000),
(44000,32000,38000,30000)

SELECT * FROM #MAXCOLUMNVALUES
-- Select Max value from Multiple columns as column name MAXSalary

SELECT
*, (SELECT MAX(MaxSalary) FROM (VALUES (InfySalary),(WiproSalary),(SyneSalary),(TCSSalary)) AS Salary(MaxSalary)) AS MAXSalary
 FROM #MAXCOLUMNVALUES