Offset functionality in Hive

Question

How can I achieve the same functionality as SQL's "offset" in Hive?

SELECT * from table LIMIT 20 OFFSET 30

Thanks!

Answer 1

Limit works with 2 arguments. Limit (count) and Limit offset,count.

So please use the 2nd option. With

select salary from employee order by salary desc limit 0,1

you will get the highest salary.

Here (Offset) 0 - first row and count (1)

Answer 2

I am unaware of a built-in function or UDF that will mimic this behavior but if you are using HIVE 0.13 you could use the row_number() function in a round-about way to get the desired result.

select pk, col_1, col_2, ... , col_n
from (
    select pk, col_1, col_2, ... , col_n, row_number() OVER (ORDER by pk) as rank
    from some_database.some_table
    ) x
where rank between 31 and 50