End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
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.