CODEWITHSUNDEEP
hive
captain ahab
captain ahab

Reputation: 1029

Find most recent date in a table using HIVE

I just need to make a simple query of a table on a MapR cluster in that I want to know what the date is of the most recent record in the table. Dates are in a 'report_date' column in string format. I tried the following query without success:

select max(report_date) from partition.table_name

I know the second part of the statement works. Is there something wrong with the first part?

Thanks, A

Upvotes: 3

Views: 5493

Answers (1)

K S Nidhin
K S Nidhin

Reputation: 2650

Your date column datatype is string hence the max function doesnt produce the output as desired.

for example : string column with values 1,2,3,4 and when you run max(column) you wont get the output as 4 , since max doesnt work on string datatype.

Try changing your datatype to DATE or TIMESTAMP , Which should work.

OR

if changing datatype is not possible then try,

  1. If there is an auto incrementing ID column in the table or any column like so , then
  2. select report_date from table_name order by ID desc.

This should provide you the max date sting.

Upvotes: 1

Related Questions