Introduction
Simple OVER()
PARTITION BY
Ranking functions
15. Ranking Functions – Review and Exercise 1
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.