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 (
RANK() OVER (ORDER BY Col2 ASC) AS Rank,
WHERE Rank = Place;
Got it? Then it's time for a short quiz!