Introduction
Quick Refresher
PARTITION BY ORDER BY with Ranking
PARTITION BY ORDER BY with Window Frames
Using PARTITION BY ORDER BY with Analytical Functions
17. FIRST_VALUE() with PARTITION BY ORDER BY
Summary and Review

Instruction

Good job! Of course, other analytical functions can also work. Let's analyze another example:

SELECT
  Country,
  City,
  Rating,
  FIRST_VALUE(City) OVER(PARTITION BY Country ORDER BY Rating DESC) AS FirstCity
FROM Store;

In the above query, we're showing each store individually and the city with the highest rating in that particular country. Note that this would be impossible without PARTITION BY – we couldn't get individual city names for each country separately.

Exercise

Show sales figures for August 1 to August 3. For each store, show the StoreId, the Day, the Revenue, and the date with the best revenue in that period (as BestRevenueDay).

Stuck? Here's a hint!

Use:

FIRST_VALUE(Day) OVER(PARTITION BY StoreId ORDER BY Revenue DESC)