[Draftting] Ranking with Window Functions

Ranking functions are used to rank rows based on a given partitioning expression and order expression.

The results are declared in sets of rows which is referred to as "window" - that's why it's called window function! 😉

Here's a generic syntax of the ranking functions.

Don't worry if you don't understand the syntax - we will explain them step-by-step in the following example.

Using the table containing the running speed in seconds for 20 athletes, we will explore the differences between the RANK(), DENSE_RANK(), and ROW_NUMBER() window functions.

Example 1: Ranking with ORDER BY

Output the list of athletes in the ascending order of running speed.

From the results, we can observe that

• ROW_NUMBER() (aliased ) assigns the rank 1 to 20 to the 20 athletes.
• RANK() (aliased ) also assigns the rank 1 to 20, but there are gaps in the ranking which we will explain below.
• DENSE_RANK() (aliased ) assigns the rank 1 to 14 only - clearly some of the athletes are receiving the same ranking.

Let's interpret the results together! We will use #1 in the screenshot above as an example.

• ROW_NUMBER() assigns ranks 3 and 4 to Amalia Handayani and Ida Puspita who have ties in their running speed of 23 seconds.
• RANK() and DENSE_RANK() respect that both athletes have the same running speed and assign rank 3 to both.

Here's where the differences start.

Notice that in the immediate next row, Dagel Winarno is ranked 5th using ROW_NUMBER() and RANK() but ranked 4th with DENSE_RANK().

That's because RANK() assigns the next rank by skipping over the number of ties and leaving a gap in the ranking, whereas DENSE_RANK() assigns the next rank on an incremental of +1 with no gaps.

Still confused? Study the rest of #2, #3, #4, and #5 and you'll better understand how the ranking works.

Let's look at another example.

Example 2: Ranking with PARTITION BY and ORDER BY

Output the list of athletes by country in the ascending order of running speed.

There are 4 sets of rows partitioned by country. Notice that the ranking is reset in each country window. That's because we added a expression.

For example, when all the athletes in China have been assigned a rank, the rank for the next group, Indonesia is reset to 1, creating a new window for Indonesian athletes' ranking.

Now that you understand how the ranking functions work, let's go and practice some questions from DataLemur!

Test Your Knowledge with DataLemur Questions