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

Instruction

OK, the query worked the way we expected.

Now, let's do the second step. This uses our previous example as a subquery and puts it in the FROM clause. We previously wrote:

SELECT
  Name,
  RANK() OVER(ORDER BY EditorRating DESC) AS Rank
FROM Game;

and now we'll write this:

WITH Ranking AS (
  SELECT
    Name,
    RANK() OVER(ORDER BY EditorRating DESC) AS Rank
  FROM Game
)

SELECT
  Name
FROM Ranking
WHERE Rank = 2;

The first line (WITH Ranking AS) tells SQL Server that the code that follows is named ranking. Inside the parentheses, we put the query created in the previous step. At the end, all we do is select the row(s) where Rank = 2 from the query named Ranking.

Exercise

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