Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

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)