CODEWITHSUNDEEP
hadoophiveaggregate-functions
Sai Mammahi
Sai Mammahi

Reputation: 227

First() in hive

i have data like below. so I would like to group by year, name and the first value of name.

year,name,id
2017,AAA,101
2017,AAA,102
2017,AAA,221
2018,BBB,110
2018,BBB,112
2019,CCC,501
2019,CCC,504

i would like to get the output like below

2017,AAA,101
2018,BBB,110
2019,CCC,501

Upvotes: 2

Views: 1013

Answers (1)

leftjoin
leftjoin

Reputation: 38315

For your data example simple row_number will work:

select year,name,id
  from
      (
       select year,name,id, 
              row_number() over(partition by year order by id) rn 
         from mytable
      )s
 where rn=1

Upvotes: 2

Related Questions