Introduction
Selecting n-th row
19. The full query
Revision

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. So, just for revision, 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

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

Console

Code editor

Result

TableConsole