Implementing Oracle's rownum() in Apache Impala-CodePudding

I am converting an Oracle query into Impala equivalent. I have a Oracle query like this:

select c1, c2 from t1 
where rownum <= (select c3 from t2 where c4 = 'Some string' and c5 = 'some string')
and c2 in (1,2,3) order by c3 asc;

However Impala does not support rownum() that I came to know while researching. Please help me in implementing this in Impala.

Thank you in advance.

CodePudding user response：

You dont have anything like rownum in oracle. However you can create a pseudo column using row_number() over (partition by col, order by col) function. You need to avoid partition by clause.
You can change your sql and add a subquery to calculate rownum column like below. Also you need to change your query so it works in impala using join instead of the way you wrote above.

select c1, c2, c3 from 
(select c1,c2, row_number() over (order by c1) as rownum from t1 ) t1
join (select c3 from t2 where c4 = 'Some string' and c5 = 'some string')
and c2 in (1,2,3)) t2 on
rownum<=t2.c3  
order by c3 asc;

CodePudding user response：

According to the documentation, you can use row_number of Impala as it is. Which means your query would be executed successfully if you try this way:

select column from table
where row_number = 1;