FB

SQL RANK and DENSE_RANK: Function Guide, FAQ, and Examples

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.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.