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
9. Using PARTITION BY ORDER BY in a CTE
PARTITION BY ORDER BY with Window Frames
Using PARTITION BY ORDER BY with Analytical Functions
Summary and Review

Instruction

All right! Do you remember CTEs? We introduced them earlier; they use the WITH statement to name a result set. We used them to find rows with a certain rank. Let's see how CTEs can work with PARTITION BY ORDER BY.

In the query below, we use CTEs to find even more rows with a certain rank, each for a different group. Take a look:

WITH Ranking AS (
  SELECT
    Country,
    City,
    RANK() OVER(PARTITION BY Country ORDER BY Rating DESC) AS Rank
  FROM Store
)

SELECT
  Country,
  City
FROM Ranking
WHERE Rank = 1;

The CTE in the parentheses creates a separate ranking (based on ratings) of stores for each country. In the outer query, we simply return the rows with the right rank. As a result, we'll see the best store in each country.

Exercise

For each store, show a row with three columns: StoreId, the highest daily Revenue for that store, and the Day when that revenue was achieved.

Stuck? Here's a hint!

Use PARTITION BY StoreId ORDER BY Revenue DESC.