How do I get max value from different columns from a row in HIVE?
For instance
Row# ID# Col1 Col2 Col3
1 1234 54 67 86
2 5678 89 92 86
...
...
Looking for output of the form:
1234 86
5678 92
Thanks!
How do I get max value from different columns from a row in HIVE?
For instance
Row# ID# Col1 Col2 Col3
1 1234 54 67 86
2 5678 89 92 86
...
...
Looking for output of the form:
1234 86
5678 92
Thanks!
Hive has the greatest() function as of 1.1;
select ID, greatest(col1, col2, col3) as greatest_value from table;
Or you can use a case when statement if your version doesn't have greatest():
select ID
, case
when col1 > col2 and col1 > col3 then col1
when col2 > col3 then col2
else col3
end as greatest_value
from table
;
Case when statements are evaluated in order from top to bottom until a value of true is found, so no need to evaluate two inequalities in each when clause.