CODEWITHSUNDEEP
hadoophivehiveql
Praveen Muthusamy
Praveen Muthusamy

Reputation: 1

hive : select row with column having maximum value without join

writing hive query over a table to pick the row with maximum value in column there is table with following data for example: 

key    value    updated_at
  1    "a"      1  
  1    "b"      2  
  1    "c"      3

the row which is updated last needs to be selected. currently using following logic 

select tab1.* from table_name tab1
join select tab2.key , max(tab2.updated_at) as max_updated from table_name tab2
on tab1.key=tab2.key and tab1.updated_at = tab2.max_updated;

Is there any other better way to perform this?

Upvotes: 0

Views: 5336

Answers (1)

rchang
rchang

Reputation: 5236

If it is true that updated_at is unique for that table, then the following is perhaps a simpler way of getting you what you are looking for:

-- I'm using Hive 0.13.0
SELECT * FROM table_name ORDER BY updated_at DESC LIMIT 1;

If it is possible for updated_at to be non-unique for some reason, you may need to adjust the ORDER BY logic to break any ties in the fashion you wish.

Upvotes: 1

Related Questions