Introduction
PARTITION BY
Ranking functions
Window Frames
Analytic Functions
PARTITION BY ORDER BY
Order of Evaluation
Finished!
Instruction
Great! Let's move on to ranking functions, which appeared in Part 4. Take a look at this example:
WITH Ranking AS (
SELECT
Title,
RANK() OVER(ORDER BY EditorRating DESC) AS Rank
FROM Movie
)
SELECT
Title
FROM Ranking
WHERE Rank = 2;
In the above example, we show the Title of the second best movie in terms of the EditorRating.
Exercise
For each movie, show the following information: Title, ReleaseYear, EditorRating and the Rank based on EditorRating. The movie with the highest EditorRating should have Rank = 1. The same rank values are possible for multiple rows, but don't leave any gaps in the numbering.
Stuck? Here's a hint!
Use DENSE_RANK() and order by the EditorRating in the descending order.
