The RANK and DENSE_RANK functions are advanced functions but they don’t need to be complicated. Learn how to use the Oracle RANK function and Oracle DENSE_RANK function in this article.
Purpose of the Oracle RANK Function
The Oracle RANK function allows you to calculate the rank of a value in a group of values. It returns a NUMBER value.
It can be used in two ways in Oracle – as an aggregate function, or as an analytical function.
I’ll look at both of them in this article.
Syntax
The syntax for the RANK function depends on your usage of it
Using RANK as an aggregate function (takes multiple rows and returns a single number):
RANK ( expr ) WITHIN GROUP ( ORDER BY ( order_expr [NULLS FIRST/LAST] ) )Using RANK as an analytical function (takes criteria and shows a number for each record)”
RANK () OVER ( [query_partition_clause] order_by_clause )This is a little more complicated than many other Oracle functions, but I’ll explain the function in this article.
Parameters – Aggregate RANK Function
The parameters of the RANK function are different, depending on whether you’re using it as an aggregate or analytical function
For an aggregate function, the parameters are:
- expr (mandatory): This is the value to search for to find a particular record in the table. You can specify more than one expr value, separated by a comma.
- expr is ordered by to determine a RANK value. This is because you get different RANK values depending on what order you want to rank data in.
- NULLS FIRST/LAST (optional): This option specifies whether you want to have NULLS at the top or the bottom of the ranking order. NULLS FIRST means that NULL values are considered a high rank, and NULLS LAST means that NULL values are considered a low rank.
Some notes about this function used as an aggregate:
- The number of expressions within RANK (expr) must be the same as the number of expressions in the ORDER BY clause.
- The expression lists in RANK and ORDER BY are matched by position, so they must be the same data type.
Parameters – Analytical RANK Function
If you’re using RANK as an analytical function (i.e. one RANK value per record returned), then the parameters are:
- query_partition_clause (optional): This is the expression to “group” the ranking by. You can rank data within groups in your query, and the PARTITION BY does this.
- order_by_clause (mandatory): The expression to order your results by to determine a rank.
Some notes:
- If two records have the same values according to the RANK function, then they will have the same RANK value. This will then cause a “gap” in the rankings. The DENSE_RANK function is similar to RANK but does not cause a gap in the rankings.
Purpose of the Oracle DENSE_RANK Function
The purpose of the DENSE_RANK function is to calculate a rank of a row in a group of rows, and returns this rank as a NUMBER value.
It can be used as either an aggregate function or an analytical function.
Let’s look at both of them in this article.
Syntax
The syntax of DENSE_RANK depends on how you use it.
Using it as an aggregate function (which takes multiple rows and returns a single number):
DENSE_RANK ( expr, [expr(n)] ) WITHIN GROUP ( ORDER BY (order_expr [ASC|DESC] [NULLS FIRST|LAST] )Using it as an analytical function (takes criteria and shows a number for each record):
DENSE_RANK() OVER ( [query_partition_clause] order_by_clause)This is a little more complicated than many other Oracle functions, but I’ll explain the function in this article.
Parameters – Aggregate DENSE_RANK Function
The parameters for this function are different, depending on whether you use it as an aggregate function or an analytical function.
The parameters of the Oracle DENSE_RANK function when used as an aggregate function are:
- expr (mandatory): This is the value to search for to find a particular record in the table. You can specify more than one expr value, separated by a comma.
- expr by. THis is because you get different values depending on what order you rank the data in.
- NULLS FIRST|LAST (optional). This option specifies whether you want to have NULLS at the top or the bottom of the ranking order. NULLS FIRST means that NULL values are considered a high rank, and NULLS LAST means that NULL values are considered a low rank.
- The number of expressions within DENSE_RANK (expr) must be the same as the number of expressions in the ORDER BY clause.
- The expression lists in DENSE_RANK and ORDER BY are matched by position, so they must be the same data type.
Parameters – Analytical DENSE_RANK Function
Now, let’s take a look at the parameters if you’re using DENSE_RANK as an analytical function:
- query_partition_clause (optional): This is the expression to “group” the ranking by. You can rank data within groups in your query, and the PARTITION BY does this.
- order_by_clause (mandatory): The expression to order your results by to determine a rank.
Some notes:
- If two records have the same values according to the DENSE_RANK function, then they will have the same RANK value. However, this will NOT cause a “gap” in the rankings. It is a similar function to RANK but does not cause a gap in the rankings.
What’s the Difference Between RANK, DENSE_RANK, and ROW_NUMBER in Oracle?
The main differences between RANK, DENSE_RANK, and ROW_NUMBER in Oracle are:
- RANK is based on a column or expression and may generate the same value twice. Rank numbers are skipped so there may be a gap in rankings.
- DENSE_RANK is also based on a column or expression and may generate the same value twice. Rank numbers are not skipped so there will not be a gap in rankings.
- ROW_NUMBER is generated once for each row so there are no duplicates or gaps.
Examples of the Oracle RANK Function
Here are some examples of the Oracle RANK function. I find that examples are the best way for me to learn about code, even with the explanation above.
Sample data
This is the student table that is being used for these examples.
SELECT *
FROM student
ORDER BY fees_paid;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_PAID | GENDER |
| 5 | Steven | Webber | 0 | M |
| 10 | Tanya | Hall | 50 | F |
| 4 | Mark | Holloway | 100 | M |
| 6 | Julie | Armstrong | 150 | F |
| 7 | Michelle | Randall | 150 | F |
| 1 | John | Smith | 200 | M |
| 3 | Tom | Capper | 350 | M |
| 2 | Susan | Johnson | 500 | F |
| 8 | Andrew | Cooper | 800 | M |
| 9 | Robert | Pickering | 900 | M |
Example 1 (Aggregate) – Basic
This example ranks the value of 200 in the fees_paid column.
SELECT
RANK(200) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;Result:
| RANK_VAL |
| 6 |
It shows 6, because the value of 200 is the 6th lowest in the fees_paid column in the student table.
Example 2 (Aggregate) – Duplicate value
This example uses a value that is duplicated in the fees_paid column
SELECT
RANK(150) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;Result:
| RANK_VAL |
| 4 |
It shows 4, because the value of 150 is the 4th lowest in the fees_paid column in the student table.
Example 3 (Aggregate) – Second column
This example uses the same duplicated value but searches for a second column.
SELECT
RANK(150, 'Minson') WITHIN GROUP (ORDER BY fees_paid, last_name) AS rank_val
FROM student;Result:
| RANK_VAL |
| 5 |
It shows 5, because even though the value of 150 is the 4th lowest in the fees_paid column in the student table, it also searches by last_name, which comes after Armstrong but before Randall.
Example 4 (Aggregate) – Using NULLS FIRST
This example uses the NULLS FIRST parameter.
SELECT
RANK(150) WITHIN GROUP (ORDER BY fees_paid NULLS FIRST) AS rank_val
FROM student;Result:
| RANK_VAL |
| 4 |
It still shows a value of 4, because there are no NULL values in this column.
Example 5 (Aggregate) – Use Name, but number in Order By
This example attempts to specify a name value, but ordering by the fees_paid column.
SELECT
RANK('Tom') WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;Result:
ORA-01722: invalid numberThis causes an ORA-01722 invalid number error, because the RANK parameter needs to be the same data type as the ORDER BY parameter.
Example 6 (Aggregate) – Use a text value
This example performs a RANK function using a text value (the first_name).
SELECT
RANK('Steven') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;Result:
| RANK_VAL |
| 7 |
The result is 7, because the name Steven is 7th in the list when ordered by first_name.
Example 7 (Aggregate) – Use a text value that is not in the database.
This example ranks a first_name value that is not in the database table.
SELECT
RANK('Brad') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;Result:
| RANK_VAL |
| 2 |
The result is 2. Even though Brad is not a record in the table, it would still have been ranked #2.
Example 8 (Analytical) – Partition by gender
This example partitions (groups) the data by gender, then ranks the values according to fees_paid.
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val
FROM student;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | GENDER | FEES_PAID | RANK_VAL |
| 10 | Tanya | Hall | F | 50 | 1 |
| 7 | Michelle | Randall | F | 150 | 2 |
| 6 | Julie | Armstrong | F | 150 | 2 |
| 2 | Susan | Johnson | F | 500 | 4 |
| 5 | Steven | Webber | M | 0 | 1 |
| 4 | Mark | Holloway | M | 100 | 2 |
| 1 | John | Smith | M | 200 | 3 |
| 3 | Tom | Capper | M | 350 | 4 |
| 8 | Andrew | Cooper | M | 800 | 5 |
| 9 | Robert | Pickering | M | 900 | 6 |
As you can see, each row has a RANK value. There is a duplicate within a gender, as that is expected in the RANK function. The RANK values are separated for M and F gender values.
Example 9 (Analytical) – Partition by fees paid
This example partitions the data by fees_paid, and ranks according to last name, and first name.
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (PARTITION BY fees_paid ORDER BY last_name, first_name) AS rank_val
FROM student;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | GENDER | FEES_PAID | RANK_VAL |
| 5 | Steven | Webber | M | 0 | 1 |
| 10 | Tanya | Hall | F | 50 | 1 |
| 4 | Mark | Holloway | M | 100 | 1 |
| 6 | Julie | Armstrong | F | 150 | 1 |
| 7 | Michelle | Randall | F | 150 | 2 |
| 1 | John | Smith | M | 200 | 1 |
| 3 | Tom | Capper | M | 350 | 1 |
| 2 | Susan | Johnson | F | 500 | 1 |
| 8 | Andrew | Cooper | M | 800 | 1 |
| 9 | Robert | Pickering | M | 900 | 1 |
As you can see, most of the RANK values are 1 because the fees_paid is mostly unique. Where the fees_paid is not unique, it has been ranked based on last_name then first_name, causing a value of 2 for Michelle Randall.
Example 10 (Analytical) – No partition
This example uses the RANK function without a partition clause.
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (ORDER BY fees_paid) AS rank_val
FROM student;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | GENDER | FEES_PAID | RANK_VAL |
| 5 | Steven | Webber | M | 0 | 1 |
| 10 | Tanya | Hall | F | 50 | 2 |
| 4 | Mark | Holloway | M | 100 | 3 |
| 6 | Julie | Armstrong | F | 150 | 4 |
| 7 | Michelle | Randall | F | 150 | 4 |
| 1 | John | Smith | M | 200 | 6 |
| 3 | Tom | Capper | M | 350 | 7 |
| 2 | Susan | Johnson | F | 500 | 8 |
| 8 | Andrew | Cooper | M | 800 | 9 |
| 9 | Robert | Pickering | M | 900 | 10 |
Each row has been assigned a RANK value, according to fees_paid.
Examples of the DENSE_RANK Function
Here are some examples of the Oracle DENSE_RANK function. I find that examples are the best way for me to learn about code, even with the explanation above.
Sample Data
We’re using a student database table for the examples here.
SELECT * FROM student ORDER BY fees_paid;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_REQUIRED | FEES_PAID | GENDER |
| 6 | Julie | Armstrong | 100 | 0 | F |
| 5 | Steven | Webber | 100 | 80 | M |
| 1 | John | Smith | 500 | 100 | M |
| 9 | Robert | Pickering | 110 | 100 | M |
| 10 | Tanya | Hall | 150 | 150 | F |
| 2 | Susan | Johnson | 150 | 150 | F |
| 3 | Tom | Capper | 350 | 320 | M |
| 8 | Andrew | Cooper | 800 | 400 | M |
| 4 | Mark | Holloway | 500 | 410 | M |
| 7 | Michelle | Randall | 250 | F |
Example 1 (Aggregate) – Basic
This example ranks the value of 100 in the fees_paid column.
SELECT
DENSE_RANK(100) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;Result:
| RANK_VAL |
| 3 |
It shows 3, because the value of 100 is the 3rd lowest in the fees_paid column.
Example 2 (Aggregate) – Duplicate Value
This example uses a value that is duplicated in the fees_paid column.
SELECT
DENSE_RANK(150) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;Result:
| RANK_VAL |
| 4 |
It shows 4, because 150 is the 4th lowest in the fees_paid column in the student table.
Example 3 (Aggregate) – Second column
This example uses the same duplicated value, but searches for a second column.
SELECT
DENSE_RANK(150, 'Minson') WITHIN GROUP (ORDER BY fees_paid, last_name) AS rank_val
FROM student;Result:
| RANK_VAL |
| 7 |
It shows 7, because even though the value of 150 is the 4th lowest in the fees_paid column in the student table, it also searches by last_name, which comes after Armstrong but before Randall.
Example 4 (Aggregate) – Using NULLS FIRST
This example uses the NULLS FIRST parameter.
SELECT
DENSE_RANK(150) WITHIN GROUP (ORDER BY fees_paid NULLS FIRST) AS rank_val
FROM student;Result:
| RANK_VAL |
| 5 |
It shows a value of 5, because there are no NULL values in this column.
Example 5 (Aggregate) – Use Name, but number in Order By
This example attempts to specify a name value, but ordering by the fees_paid column.
SELECT
DENSE_ RANK('Julie') WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;Result:
ORA-01722: invalid numberThis causes an error, because the DENSE_ RANK parameter needs to be the same data type as the ORDER BY parameter.
Example 6 (Aggregate) – Use a text value
This example performs a DENSE_RANK function using a text value (the first_name).
SELECT
DENSE_ RANK('Julie') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;Result:
| RANK_VAL |
| 3 |
The result is 3, because the name Julie is 3rd in the list when ordered by first_name.
Example 7 (Aggregate) – Use a text value that is not in the database.
This example ranks a first_name value that is not in the database table.
SELECT
DENSE_RANK('Boris') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;Result:
| RANK_VAL |
| 2 |
The result is 2. This is because Boris is not in the table, but if he was, he would be ranked number 2.
Example 8 (Analytical) – Partition by gender
This example partitions (groups) the data by gender, then ranks the values according to fees_paid.
SELECT
student_id, first_name, last_name, gender, fees_paid,
DENSE_RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val
FROM student;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | GENDER | FEES_PAID | RANK_VAL |
| 6 | Julie | Armstrong | F | 0 | 1 |
| 2 | Susan | Johnson | F | 150 | 2 |
| 10 | Tanya | Hall | F | 150 | 2 |
| 7 | Michelle | Randall | F | 3 | |
| 5 | Steven | Webber | M | 80 | 1 |
| 1 | John | Smith | M | 100 | 2 |
| 9 | Robert | Pickering | M | 100 | 2 |
| 3 | Tom | Capper | M | 320 | 3 |
| 8 | Andrew | Cooper | M | 400 | 4 |
| 4 | Mark | Holloway | M | 410 | 5 |
As you can see, each row has a DENSE_RANK value. There is a duplicate within a gender, as that is expected in the DENSE_RANK function. The RANK values are separated for M and F gender values.
Example 9 (Analytical) – Partition by fees paid
This example partitions the data by fees_paid, and ranks according to last name, and first name.
SELECT
student_id, first_name, last_name, gender, fees_paid,
DENSE_RANK() OVER (PARTITION BY fees_paid ORDER BY last_name, first_name) AS rank_val
FROM student;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | GENDER | FEES_PAID | RANK_VAL |
| 6 | Julie | Armstrong | F | 0 | 1 |
| 5 | Steven | Webber | M | 80 | 1 |
| 9 | Robert | Pickering | M | 100 | 1 |
| 1 | John | Smith | M | 100 | 2 |
| 10 | Tanya | Hall | F | 150 | 1 |
| 2 | Susan | Johnson | F | 150 | 2 |
| 3 | Tom | Capper | M | 320 | 1 |
| 8 | Andrew | Cooper | M | 400 | 1 |
| 4 | Mark | Holloway | M | 410 | 1 |
| 7 | Michelle | Randall | F | 1 |
As you can see, most of the DENSE_RANK values are 1 because the fees_paid is mostly unique. Where the fees_paid is not unique, it has been ranked based on last_name then first_name, causing a value of 2 for Michelle Randall.
Example 10 (Analytical) – No partition
This example uses the RANK function without a partition clause.
SELECT
student_id, first_name, last_name, gender, fees_paid,
DENSE_RANK() OVER (ORDER BY fees_paid) AS rank_val
FROM student;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | GENDER | FEES_PAID | RANK_VAL |
| 6 | Julie | Armstrong | F | 0 | 1 |
| 5 | Steven | Webber | M | 80 | 2 |
| 1 | John | Smith | M | 100 | 3 |
| 9 | Robert | Pickering | M | 100 | 3 |
| 10 | Tanya | Hall | F | 150 | 4 |
| 2 | Susan | Johnson | F | 150 | 4 |
| 3 | Tom | Capper | M | 320 | 5 |
| 8 | Andrew | Cooper | M | 400 | 6 |
| 4 | Mark | Holloway | M | 410 | 7 |
| 7 | Michelle | Randall | F | 8 |
Each row has been assigned a DENSE_RANK value, according to fees_paid.
Example 11 – Compare RANK and DENSE_RANK
This example is similar to example 8, but we are using both RANK and DENSE_RANK to see the differences.
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val,
DENSE_RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS dense_rank_val
FROM student;Result:
| STUDENT_ID | FIRST_NAME | LAST_NAME | GENDER | FEES_PAID | RANK_VAL | DENSE_RANK_VAL |
| 6 | Julie | Armstrong | F | 0 | 1 | 1 |
| 2 | Susan | Johnson | F | 150 | 2 | 2 |
| 10 | Tanya | Hall | F | 150 | 2 | 2 |
| 7 | Michelle | Randall | F | 4 | 3 | |
| 5 | Steven | Webber | M | 80 | 1 | 1 |
| 1 | John | Smith | M | 100 | 2 | 2 |
| 9 | Robert | Pickering | M | 100 | 2 | 2 |
| 3 | Tom | Capper | M | 320 | 4 | 3 |
| 8 | Andrew | Cooper | M | 400 | 5 | 4 |
| 4 | Mark | Holloway | M | 410 | 6 | 5 |
Both the RANK and DENSE_RANK take the same parameters, but you can see the output is different. RANK leaves some numbers out of the sequence, but DENSE_RANK does not show any gaps.
Image courtesy of digitalart / FreeDigitalPhotos.net
