Introduction
Selecting n-th row
17. Explanation
Revision

Instruction

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

In this section, we will learn how to create queries that, for instance, return only the row with rank 1, 5, 10, etc. This cannot be accomplished with a simple query – we will need to create a complex one. For this purpose, we'll use Common Table Expressions. An example may look like 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 query returns the name of the game which gets rank number 2 with respect to editor rating. Don't worry if the query looks complicated. We'll explain it in a second.

Exercise

Press Next exercise to continue.

Console

Code editor

Result

TableConsole