SQL: How to select COLUMN with max value in Redshift?

Question

How can I extract maximum column of a row in Redshift table? e.g. one of my rows is: value1, value2, value3, value4, value5 100, 56, 516, 1000, 516

I would like to see value4: 1000 as a result?

Thanks

Answer 1

In Redshift, you can use greatest():

select t.*, greatest(value1, value2, value3, value4) as greatest_value
from mytable t

Note that greatest() (as well as least()) ignores null values; on the other hand, if all values are null, you get null as a result.

If you want to identify from which column the greatest value comes from, then use a case expression:

select t.*, greatest(value1, value2, value3, value4) as greatest_value,
    case greatest(value1, value2, value3, value4)
        when value1 then 'value1'
        when value2 then 'value2'
        when value3 then 'value3'
        when value4 then 'value4'
    end as greatest_column
from mytable t

Ties are an edge case here; you don't specify which logic you want in that event, so: the above query gets the first top tie column, as defined by the order of the branches in the case expression.