Oracle RANK and DENSE_RANK Function Guide, FAQ, and Examples

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 number

This causes an 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 number

This 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