I need to fetch the maximum value from multiple columns in a single row. The row looks like this:
Col1 || Col2 || Col3 || Col4 || 100 || 120 || 130 || 140 || 100 || 130 || 130 || 140 || 100 || 140 || 130 || 140 || I need to fetch max of these columns where col2=120 so that the result is returned as col4 value which is 140
This is using MSSQL Server
SELECT ( SELECT Max(v) FROM (VALUES (Col1), (Col2), (Col3),(Col4)) AS value(v) ) as [MaxDate] FROM [TableName] WHER Col2 = 120 
Since it's only 4 columns, one way would be to simply use case:
SELECT CASE WHEN Col1 >= Col2 AND Col1 >= Col3 AND Col1 >= Col4 THEN Col1 WHEN Col2 >= Col1 AND Col2 >= Col3 AND Col2 >= Col4 THEN Col2 WHEN Col3 >= Col1 AND Col3 >= Col2 AND Col3 >= Col4 THEN Col3 WHEN Col4 >= Col1 AND Col4 >= Col2 AND Col4 >= Col3 THEN Col4 END As Max FROM TableName WHERE Col2 = 120 
