Hive / SQL query for top n values per key

Question

I want top 2 valus per key. The result would look like:

What should be the hive query.

Answer 1

You can use a window function with OVER() close:

select col1,col2 from (SELECT col1,
col2, 
ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 DESC) AS row_num
FROM data)f
WHERE f.row_num < 3
order by col1,col2