In this article, you’ll learn everything about the SQL RANK and DENSE_RANK functions that are available in Oracle, SQL Server, MySQL, and Postgres.
What are the RANK and DENSE_RANK SQL Functions?
The RANK and DENSE_RANK functions allow you to calculate the rank, or the position in a list, of a value in a group of values. It returns a number value.
What’s the Difference between RANK and DENSE_RANK and ROW_NUMBER?
The RANK and DENSE_RANK functions are slightly different from each other as well as the ROW_NUMBER function:
- RANK numbers are skipped so there may be a gap in rankings, and may not be unique.
- DENSE_RANK numbers are not skipped so there will not be a gap in rankings, and may not be unique.
- ROW_NUMBER is generated once for each row so there are no duplicates or gaps.
For example:
- RANK: a list of results could use the RANK function and show values of 1, 2, 2, 4, and 5. The number 3 is skipped because the rank of 2 is tied.
- DENSE_RANK: a list of results could use the DENSE_RANK function and show values of 1, 2, 2, 3, and 4. The number 3 is still used, even if rank of 2 is tied.
- ROW_NUMBER: a list of results could use the ROW_NUMBER function and show values of 1, 2, 3, 4, and 5. All of the numbers are unique.
Syntax
The syntax for the SQL RANK function is:
RANK () OVER ( [query_partition_clause] order_by_clause )The syntax for the SQL DENSE_RANK function is similar:
DENSE_RANK () OVER ( [query_partition_clause] order_by_clause )This follows a similar format to other window functions. To learn more about window functions, check out my guide: SQL Window Functions: The Ultimate Guide.
You can also use these functions as an aggregate function. You provide an input value, and the function returns the rank of where that value would be.
Here’s the syntax for both functions:
RANK (search_value) WITHIN GROUP ( [query_partition_clause] order_by_clause );
DENSE_RANK (search_value) WITHIN GROUP ( [query_partition_clause] order_by_clause );
Parameters
The parameters for both RANK and DENSE_RANK are:
- query_partition_clause (optional): This is the expression to “group” the ranking by. You can rank data within groups in your query. This is specified using PARTITION BY and is how you can perform a “rank over partition by”.
- order_by_clause (mandatory): The expression to order your results by to determine a rank. This is specified using ORDER BY.
What Databases Support RANK and DENSE_RANK?
The SQL RANK and DENSE_RANK functions are available in:
- Oracle
- SQL Server
- MySQL (8.0 and later)
- Postgres
Does MySQL 5.7 support RANK or DENSE_RANK? No, it’s only available in 8.0 and newer.
Examples of the SQL RANK Function
Here are some examples of the SQL RANK function.
Sample data
This is the student table that is being used for these examples. You can get the SQL scripts for this sample data in my GitHub repository here.
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) – 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 5 (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 6 (Window) – Partition by Gender
This example partitions (groups) the data by gender, and 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 the gender of F, as that is expected in the SQL RANK function. The RANK values are separated for M and F gender values.
Example 7 (Window) – Partition by fees paid
This example partitions the data by fees_paid, and ranks according to the 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 values are mostly unique. Where the fees_paid is not unique, it has been ranked based on last_name and then first_name, causing a value of 2 for Michelle Randall.
Example 8 (Window) – 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 SQL DENSE_RANK function.
Sample Data
We’re using a student database table for the examples here. You can get the SQL scripts for this sample data in my GitHub repository 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) – 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 5 (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 6 (Window) – Partition by Gender
This example partitions (groups) the data by gender, and 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 the gender of F, as that is expected in the SQL DENSE_RANK function. The DENSE_RANK values are separated for M and F gender values.
Example 7 (Window) – Partition by fees paid
This example partitions the data by fees_paid, and ranks according to the 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 values are mostly unique. Where the fees_paid is not unique, it has been ranked based on last_name and then first_name, causing a value of 2 for Michelle Randall.
Example 8 (Window) – No partition
This example uses the SQL DENSE_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 9 – 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.
Conclusion
Both of these functions are useful and exist for several database vendors.
If you have any questions on these functions, let me know in the comments below.