CODEWITHSUNDEEP
sqljoinhivehiveql
kashmoney
kashmoney

Reputation: 97

Hive - Get a max values from a bunch of columns

I have some information like this:

id. amt.  date.  
1.  23.    10/18
1.  20.    10/14
1.  22.    10/18
2.  10.    10/13
2.  10.    10/13

I want to get the max of date and then the max of amt. If there are duplicates, I want only one.

1. 23.  10/18
2. 10.  10/13

I can accomplish this using multiple joins and filtering but I was wondering if there are any analytic functions which make it more efficient and readable. Any tricks to use efficient joins are also welcome. Thanks.

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by date desc, amt desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions