Introduction
Simple OVER()
PARTITION BY
Ranking functions
15. Ranking functions – Revision
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.

Console

Code editor

Result

TableConsole