Alright. We'll start with the most widely used ranking function: RANK(). The syntax is as follows:
RANK() OVER (ORDER BY ...)
What does RANK() do? It returns the rank of each row as a number. This rank is determined by the sorting criteria specified within the RANK() statement's brackets.
ORDER BY sorts rows and displays them in a specific order. RANK() OVER(ORDER BY ...) is a function that shows the rank (i.e. place or position) of each row in a separate column.
Let's look at an example from our database:
SELECT Name, Platform, EditorRating, RANK() OVER(ORDER BY EditorRating ASC) AS Ranking FROM Game;
The first three columns are quite obvious, but look what happens next: we want to return the RANK() of each row when we sort them by the column EditorRating in (OVER(ORDER BY EditorRating ASC)).
Look at the result of the example query on the right.
As you can see, we get the rank of each game in the last column. There are three games with the lowest possible editor rating (4). All of these games got rank 1.
The next game, with scored 5, got rank 4. Maybe we think it would be rank 2, but that's not how RANK() works. There were three games before the game that scored 5, so, being the fourth game, it got rank 4 – regardless of the fact that the other three all got rank 1.
Important: RANK() will always leave gaps in numbering when multiple rows have the same ranking value.
