Introduction
Quick refresher
PARTITION BY ORDER BY with ranking
PARTITION BY ORDER BY with window frames
PARTITION BY ORDER BY with analytical functions
17. FIRST_VALUE() with PARTITION BY ORDER BY
Revision

Instruction

Good job! Of course, other analytical functions are possible as well. Let's analyze another example:

SELECT
  country,
  city,
  rating,
  FIRST_VALUE(city) OVER(PARTITION BY country ORDER BY rating DESC)
FROM store;

In the above query, we're showing each store individually, but we also show the name of 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 in the period between August 1 and August 3: for each store, show the store_id, the day, the revenue and the date with the best revenue in that period as best_revenue_day.

Stuck? Here's a hint!

Use FIRST_VALUE(day) OVER(PARTITION BY store_id ORDER BY revenue DESC).

Console

Code editor

Result

TableConsole