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 (
RANK() OVER(PARTITION BY Country ORDER BY Rating DESC) AS Rank
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.