-6

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

Improve this question
2
  • Add a few more rows with sample data, and also specify the expected result. Commented Jul 5, 2016 at 8:53
  • 1
    Which DBMS are you using? Commented Jul 5, 2016 at 8:53

2 Answers 2

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

What does 'value(v)' mean ? What kind of alias is that ?
Please add more details to this answer.
@CesarFlores i will leave that to the reader. Learning is not giving all answers. From the query, it is easy to understand.
0

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
Improve this answer

2 Comments

As long as there are no NULL's in the table.
Any generic approach as its just an example, columns may vary for various tables

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.