How can I apply oracles rownum to impala?

Question

I want to convert an oracle query into impala.

select name,class,floor
from class.students
where name = 'ted' 
and grad ='a'
and rownum<2

Although impala can not recognize rownum.

I tried to solve it with group by in the selected columns, but I think it is not correct.

Also, rownum works as a limit or it fetches the unique row in case that we have duplicates ?

Answer 1

You can use limit to mimic oracle rownum. You can use offset too to control over number of rows. Impala limit doesn't de-duplicate and you need to use distinct to do it. Also note Impala fetches the data first and then apply limit unlike oracle.

select name,class,floor
from class.students
where name = 'ted' 
and grad ='a'
limit 2 -- This will show 2 records.