CODEWITHSUNDEEP
sqlhivehiveql
Danzo
Danzo

Reputation: 553

Selecting the the last row in a partition in HIVE

I have a table t1:

c1 | c2 | c3| c4
1    1    1   A
1    1    2   B
1    1    3   C
1    1    4   D
1    1    4   E
1    1    4   F
2    2    1   A
2    2    2   A
2    2    3   A

I want to select the last row of each c1, c2 pair. So (1,1,4,F) and (2,2,3,A) in this case. My idea is to do something like this:

create table t2 as
select *, row_number() over (partition by c1, c2 order by c3) as rank
from t1

create table t3 as
select a.c1, a.c2, a.c3, a.c4
from t2 a
inner join
(select c1, c2, max(rank) as maxrank
 from t2
 group by c1, c2
 )
on a.c1=b.c1 and a.c2=b.c1
where a.rank=b.maxrank

Would this work? (Having environment issues so can't test myself)

Upvotes: 1

Views: 6476

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Just use a subquery:

select t1.*
from (select t1.*, row_number() over (partition by c1, c2 order by c3 desc) as rank
      from t1 
     ) t1
where rank = 1;

Note the use of desc for the order by.

Upvotes: 2

Related Questions