Introduction
Selecting n-th row
Summary
22. Summary

Instruction

That was another correct answer! Okay, let's review what we've learned in this part:

  • The most basic usage of ranking functions is: RANK() OVER(ORDER BY Column1, Column2...).
  • The ranking functions we have learned are:
    • RANK() – returns the rank (a number) of each row. The sorting order is specified within the parentheses.
    • DENSE_RANK() – returns a "dense" rank, i.e. one without gaps in the numbering.
    • ROW_NUMBER() – returns a unique rank number, so even rows with the same value get different (but consecutive) numbers.
  • NTILE(x) – distributes the rows into a specific number of groups, which is given as x.

Let's also have a review query. To get Col1 of the row with a rank equal to Place in a ranking sorted by Col2, write:

WITH Ranking AS (
  SELECT
    RANK() OVER (ORDER BY Col2 ASC) AS Rank,
    Col1
  FROM TableName
)

SELECT
  Col1
FROM Ranking
WHERE Rank = Place;

Got it? Then it's time for a short quiz!

Exercise

Click Next exercise to continue.