In a Hive table, date is stored as string.
as below - considering all dates belong to same order item
10/22/2018 12:22:23.123 PM
10/22/2018 12:12:31.222 PM
10/22/2018 01:59:23.232 PM
When I apply ranking function as
select * , rank( partition by order_id order by date desc)rnk
from order_item where rnk=1
I am expecting the ranking to be
10/22/2018 01:59:23.232 PM ,1
10/22/2018 12:22:23.123 PM,2
10/22/2018 12:12:31.222 PM,3
instead I am getting below :
10/22/2018 12:22:23.123 PM,1
10/22/2018 12:12:31.222 PM,2
10/22/2018 01:59:23.232 PM,3
because date is stored as sting. It is considering 12> 01 though 01:59 is the latest record.
How to get the latest record using rank function
Note: CAST, Unixtimestamp are not no help.