CODEWITHSUNDEEP
sqlhadoophivehiveql
abc
abc

Reputation: 157

Get latest record from a table based on 2 columns in hive

I want to get the latest record from my source table based on num and id columns and insert in my target table.

Scenario is explained in the attached screen shot. For latest record date column can be used.

Screenshot

Thanks.

Upvotes: 1

Views: 656

Answers (3)

Richa Monga
Richa Monga

Reputation: 71

SELECT NUM,ID,DATE FROM TABLE_TEMP
QUALIFY RANK OVER(PARTITION BY NUM,ID ORDER BY DATE DESC)=1;

You can do this using single line query

SELECT NUM,ID,DATE FROM TABLE_TEMP QUALIFY RANK OVER(PARTITION BY NUM,ID ORDER BY DATE DESC)=1;

Upvotes: 0

Mayank Porwal
Mayank Porwal

Reputation: 34086

Select num,id, date
FROM
(
Select *, ROW_NUMBER() OVER(partition by num,id Order by date desc) as rnk
FROM source_table
)a
WHERE rnk = 1;

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

by using corelated Subquery 

 select * from your_table t 
   where t.date= (
                 select max(date) from your_table t1 
                 where t1.num=t.num and t1.id=t.id
                 )

You can do it using max() function

select num,id,max(date) from your_table t
group by num,id

Upvotes: 0

Related Questions