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

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

Click Next exercise to continue.