Support Questions

Find answers, ask questions, and share your expertise
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

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)


Super Guru


Include over() clause in your select query .

Try with below query:

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


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

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.