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.
|Gregoire Brown||United States||28|
|Loralie Yurov||United States||39|
|Randolph Willas||United States||22|
|Steffen Skelhorne||United States||46|
|William Terne||United States||28|
Output the list of athletes in the ascending order of running speed.
From the results, we can observe that
Let's interpret the results together! We will use #1 in the screenshot above as an example.
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.
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!
CTE vs. Subquery