How to PARTITION BY two columns when using FIRST_VALUE()

Question

Region	Item	Units_sold
asia	pen	1000
asia	book	5432
asia	milk	8899
north_america	pen	87
north_america	book	342
north_america	milk	8374

Above is a view I created and I'm trying to select the region and item only of the highest unist_sold row. I tried using first_value function but I can't partition by two columns.

SELECT distinct 
        region, FIRST_VALUE(units_sold)
        OVER (PARTITION BY region
                ORDER BY units_sold DESC
                RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 
                  FOLLOWING)
    AS BEST_SELLING
FROM region_view
ORDER BY region;

This gives me only the region and its corresponding highest units_sold. But I need to have the corresponding item as well.

Below is the expected outcome. Thank you.

Region	Item	Units_sold
asia	milk	8899
north_america	milk	8374

Answer 1

Your problem is really a candidate for ROW_NUMBER rather than FIRST_VALUE:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Units_sold DESC) rn
    FROM yourTable t
)

SELECT Region, Item, Units_sold
FROM cte
WHERE rn = 1;