Introduction
PARTITION BY
Ranking functions
Window frame
Analytic functions
ORDER BY PARTITION BY
Order of evaluation
Summary
Instruction
Great! Let's move on to ranking functions from Part 4. Take a look at this example:
WITH ranking AS
(SELECT
title,
RANK() OVER(ORDER BY editor_rating 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 editor_rating.
Exercise
For each movie, show the following information: title, release_year, editor_rating and the rank based on editor_rating. The movie with the highest editor_rating should have rank = 1. The same rank values are possible for multiple rows, but don't leave gaps in numbering.
Stuck? Here's a hint!
Use DENSE_RANK() and order by the editor_rating in the descending order.
