[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.

Amalia HandayaniIndonesia23
Among SalahudinIndonesia39
Bagiya HardiansyahIndonesia32
Chang ChenguangChina37
Cui XianliangChina21
Dagel WinarnoIndonesia25
Gregoire BrownUnited States28
Ida PuspitaIndonesia23
Loralie YurovUnited States39
Nonaka KichiroJapan47
Qian XiuChina46
Randolph WillasUnited States22
Saiki ShouJapan38
Shiba TetsuyaJapan48
Steffen SkelhorneUnited States46
Tanabe TomikoJapan33
Tobi SherrIndonesia39
William TerneUnited States28
Wu XinChina37
Zou XueqinChina35

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

Next Lesson

CTE vs. Subquery