"Hive" max column value from multiple columns

Question

I have a situation where I need to find the max value on 3 calculated fields and store it in another field, is it possible to do it in one SQL query? Below is the example

SELECT 
Income1, Income1 * 2% as Personal_Income, Income2, Income2 * 10% as Share_Income, Income3, Income3 * 1% as Job_Income, Max(Personal_Income, Share_Income, Job_Income) From Table

one way I tried is to calculate Personal_Income, Share_Income, Job_Income in the first pass and in the second pass I used

Select Case when Personal_income > Share_Income and Personal_Income > Job_Income then Personal_income when Share_income > Job_Income then Share_income Else Job_income as the greatest_income 

but this require me to do 2 scans on a billion rows table, How can I avoid this and do it in a single pass? Any help much appreciated.

Answer 1

You can use greatest which results in the greatest value among multiple columns on a given row.

select greatest(Income1*1.02, Income2*1.1, Income3*1.01) as greatest_Income 
From Table

Note this is not an aggregate function and other columns can be included in select as needed.