Hive SQL Ranking With Ties

Question

How to Re-Write below Query in Hive SQL I have tried the below in Oracle SQL and it works but need to write the same in Hive QL

select * from sales order by unit_price fetch with ties

Answer 1

Oracle's ORDER BY .. FETCH NEXT N ROWS WITH TIRES is used to limit the number of top N (ORDER BY is used) rows returned to the number specified by NEXT (FIRST). And with tires means that if some rows have the same order by value, they also will be returned in addition to the number of rows specified.

Hive does not have FETCH functionality. There is LIMIT, which does not support WITH TIRES.

You can achieve similar functionality using analytics dense_rank() function plus WHERE filter. For example we need to fetch 5 sales with lowest price and if there are sales with the same price, also return them. dense_rank will assign the same rank to the rows with the same price.

select * 
from
(
    select s.*, 
           dense_rank() over(order by unit_price) rnk --lowest unit_price first
                                                      --to get highest price, use order by unit_price DESC
     from sales s 
) 
where rnk<=5  --If there are rows with the same rank, they all will be returned
order by rnk