In the previous section, we introduced ranking functions, where results are shown as an additional column. Our game table is small, so it's easy to manually identify first, second, or third place. In real life, however, we deal with huge tables and looking for one particular rank can be troublesome.

In this section, we'll learn how to create queries, that return only the row(s) with a certain rank. This cannot be accomplished with a simple query – we will need to create a complex one. To do this, we'll use a Common Table Expression. For example:

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

SELECT
  Name
FROM Ranking
WHERE Rank = 2;

The query returns the name of the game that has the editor rating rank of 2.

Don't worry if the query looks complicated. You don't have to fully understand the CTE, we'll explain all the required concepts in a second. If you would like to learn more about CTE, you may want to check out our Recursive Queries for SQL Server course.