CODEWITHSUNDEEP
hadoophivehiveql
Vaishak
Vaishak

Reputation: 617

Find Max value from a list in Hive

I need to find max value from a list in HIVE.

For eg: If i am having values [5,4,3] in a list i need to get 5.

max(list_name) is not working. Is there any workaround.

Upvotes: 0

Views: 6276

Answers (4)

idso
idso

Reputation: 1

For my environment, the sort_array function will change the original array content. This may cause some unwanted results if you want to reuse the array.

You can use greatest(a[0], a[1], a[2]) to find the max element. Moreover, use a case when clause if you want to find its index.

Upvotes: 0

asPlankBridge
asPlankBridge

Reputation: 1082

You can do the following for min/max of an array:

SELECT sort_array(arr)[0] as min_arr,
sort_array(arr)[size(arr)-1] as max_arr,
FROM example;

In all the cases that I am aware of, this is more performant than a lateral view explode and group.

But I don't understand why there is no min_array(arr).

Upvotes: 2

lun zhang
lun zhang

Reputation: 51

you can see this doc

In your situation, if you have a table name example

| id | arr   |
| 1  | [1,2] |
| 2  | [3,4] |

if you want to get the result of 

| id | max_arr|
| 1  |  2     |
| 2  |  4     |

try this SQL

SELECT id, max(arr_val) as max_arr FROM example LATERAL VIEW explode(arr) arrtable AS arr_val group by id

There are only one problem, your id must be unique.

Upvotes: 2

philantrovert
philantrovert

Reputation: 10082

It might not be the most optimized, but it kinda works. 

 select max(arr) from ( select explode( array(4, 5, 1, 20, 45, 47, 9) ) as arr ) t

+------+--+
| _c0  |
+------+--+
|  47  |
+------+--+

Upvotes: 1

Related Questions