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 (a number) of each row with respect to the sorting specified within parentheses.
ORDER BY sorts rows and shows them in a specific order to you. RANK() OVER(ORDER BY ...) is a function that shows the rank(place, position) of each row in a separate column.
Let's look at an example from our database:
SELECT name, platform, editor_rating, RANK() OVER(ORDER BY editor_rating) FROM game;
The first three columns are quite obvious, but look what happens next: we want to return the rank (RANK()) of each row when we sort them by the column editor_rating (OVER(ORDER BY editor_rating).
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 3 games with the lowest score – 4. All of them got rank 1.
The next game, with score 5, got rank 4, not 2. That's how RANK() works. There were three games before the game with score 5, so, being the 4th game, it got rank 4 – regardless of the fact that the other three all got rank 1. RANK() will always leave gaps in numbering when more than 1 row share the same value.
