Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Selecting n-th row
19. The full query
Summary

Instruction

Alright, the query worked the way we wanted.

Now, the second step is to treat our previous example as a subquery and put it in the FROM clause. As you can remember, we previously wrote:

SELECT
  name,
  RANK() OVER(ORDER BY editor_rating DESC) AS rank
FROM game;

and now we'll write this:

WITH ranking AS (
  SELECT
    name,
    RANK() OVER(ORDER BY editor_rating DESC) AS rank
  FROM game
)

SELECT name
FROM ranking
WHERE rank = 2;

The first line (WITH ranking AS) tells that what follows is called ranking. Inside the parentheses, we provide the query which we created in the previous step. In the end, all we do is select the row(s) with rank = 2 from the query we named ranking.

Exercise

Click Run and check code to run the updated example to see how it works.