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
RANK() OVER(ORDER BY editor_rating DESC) AS rank
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.