Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

row_number in hive query giving error : Only COMPLETE mode supported for row_number function (state=42000,code=40000)

Highlighted

row_number in hive query giving error : Only COMPLETE mode supported for row_number function (state=42000,code=40000)

New Contributor

select row_number(),* from testDB.testTable;

Error: Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException: Only COMPLETE mode supported for row_number function (state=42000,code=40000)

1 REPLY 1

Re: row_number in hive query giving error : Only COMPLETE mode supported for row_number function (state=42000,code=40000)

Super Guru

@PP

Include over() clause in your select query .

Try with below query:

select row_number() over(),* from testDB.testTable;

Example:

select row_number() over() as rn,* 
from (
select stack(2,1,"foo",2,"bar") as (id,name)
)t; +-----+-------+---------+--+ | rn | t.id | t.name | +-----+-------+---------+--+ | 1 | 1 | foo | | 2 | 2 | bar | +-----+-------+---------+--+

"rn" is the row number column that we have added in the above result.