CODEWITHSUNDEEP
sqlhivehiveql
Bitanshu Das
Bitanshu Das

Reputation: 627

Row Number function starting from the max row number of another table in Hive

I have a query in which I require the row number to start from the max(row_number) of another table. How can I do this considering hive doesn't allow nested queries.

So my query is something like this:

Insert Overwrite Table ABC
Select row_number() over (order by Population_Count desc) + select (max(country_id))+1 as country_id,
country_name from ABC_temp;

So if the table ABC_temp has the country_id finishing at 26, The ABC table's row_count() should start from 27.

Upvotes: 1

Views: 1013

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Insert Overwrite Table ABC

Select      row_number() over (order by Population_Count desc) 
          + max(country_id) over ()

from        ABC_temp
;

Upvotes: 1

Ashish Singh
Ashish Singh

Reputation: 533

You may try this :

INSERT overwrite TABLE abc
SELECT (temp.m + row_number() over (
                                    ORDER BY population_count DESC)), country_name
FROM abc_temp ,
  (SELECT max(country_id) AS m
   FROM abc_temp) TEMP ;

Upvotes: 0

Related Questions