hive - Top N with multiple measurements in one query?

Question

The sample table

value    measurement1  measurement2
-------|-------------|-----------
value1       1           **2**
value2     **3**         **3**
value3     **2**           1

Then find top 2 highest value, I want to get the output below:

top 2 by measurement1 top 2 by measurement2 
---------------------|----------------------
      value2                  value2    
      value3                  value1

Answer 1

You can do this using row_number() and join:

select s1.value1 as col1,
       s2.value2 as col2
from (select s.*,
             row_number() over (order by value1) as seqnum
      from sample s
     ) s1 join
     (select s.*,
             row_number() over (order by value2) as seqnum
      from sample s
     ) s2
     on s1.seqnum = s2.seqnum
where s1.seqnum <= 2
order by s1.seqnum;