How can I use Coalesce and Max to extract maximum valued column which is not null

Question

I have columns A, B, C and D and would like to create new calculated column with MAX(COLUMN_A, COLUMN_B)/MAX(COLUMN_C, COLUMN_D).

Some of the rows in these columns also have NULL values, How Can I do this in Impala or SQL

Answer 1

You can use least() and greatest() for this purpose. Ignoring NULL:

select greatest(a, b) / greatest(c, d)

The problem are the NULL values. I think these functions return NULL if any argument is NULL. If the values are known to be positive, you can replace NULL with 0:

select greatest(coalesce(a, 0), coalesce(b, 0)) / greatest(coalesce(c, 0), coalesce(d, 0))

Or, with two values, you can use this construct:

select greatest(coalesce(a, b), coalesce(b, a)) / greatest(coalesce(c, d), coalesce(d, c))