FB

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.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

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.
Some notes about this function used as an aggregate:
  • 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_IDFIRST_NAMELAST_NAMEFEES_PAIDGENDER
5StevenWebber0M
10TanyaHall50F
4MarkHolloway100M
6JulieArmstrong150F
7MichelleRandall150F
1JohnSmith200M
3TomCapper350M
2SusanJohnson500F
8AndrewCooper800M
9RobertPickering900M

 

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_IDFIRST_NAMELAST_NAMEGENDERFEES_PAIDRANK_VAL
10TanyaHallF501
7MichelleRandallF1502
6JulieArmstrongF1502
2SusanJohnsonF5004
5StevenWebberM01
4MarkHollowayM1002
1JohnSmithM2003
3TomCapperM3504
8AndrewCooperM8005
9RobertPickeringM9006

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_IDFIRST_NAMELAST_NAMEGENDERFEES_PAIDRANK_VAL
5StevenWebberM01
10TanyaHallF501
4MarkHollowayM1001
6JulieArmstrongF1501
7MichelleRandallF1502
1JohnSmithM2001
3TomCapperM3501
2SusanJohnsonF5001
8AndrewCooperM8001
9RobertPickeringM9001

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_IDFIRST_NAMELAST_NAMEGENDERFEES_PAIDRANK_VAL
5StevenWebberM01
10TanyaHallF502
4MarkHollowayM1003
6JulieArmstrongF1504
7MichelleRandallF1504
1JohnSmithM2006
3TomCapperM3507
2SusanJohnsonF5008
8AndrewCooperM8009
9RobertPickeringM90010

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_IDFIRST_NAMELAST_NAMEFEES_REQUIREDFEES_PAIDGENDER
6JulieArmstrong1000F
5StevenWebber10080M
1JohnSmith500100M
9RobertPickering110100M
10TanyaHall150150F
2SusanJohnson150150F
3TomCapper350320M
8AndrewCooper800400M
4MarkHolloway500410M
7MichelleRandall250F

 

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_IDFIRST_NAMELAST_NAMEGENDERFEES_PAIDRANK_VAL
6JulieArmstrongF01
2SusanJohnsonF1502
10TanyaHallF1502
7MichelleRandallF3
5StevenWebberM801
1JohnSmithM1002
9RobertPickeringM1002
3TomCapperM3203
8AndrewCooperM4004
4MarkHollowayM4105

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_IDFIRST_NAMELAST_NAMEGENDERFEES_PAIDRANK_VAL
6JulieArmstrongF01
5StevenWebberM801
9RobertPickeringM1001
1JohnSmithM1002
10TanyaHallF1501
2SusanJohnsonF1502
3TomCapperM3201
8AndrewCooperM4001
4MarkHollowayM4101
7MichelleRandallF1

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_IDFIRST_NAMELAST_NAMEGENDERFEES_PAIDRANK_VAL
6JulieArmstrongF01
5StevenWebberM802
1JohnSmithM1003
9RobertPickeringM1003
10TanyaHallF1504
2SusanJohnsonF1504
3TomCapperM3205
8AndrewCooperM4006
4MarkHollowayM4107
7MichelleRandallF8

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_IDFIRST_NAMELAST_NAMEGENDERFEES_PAIDRANK_VALDENSE_RANK_VAL
6JulieArmstrongF011
2SusanJohnsonF15022
10TanyaHallF15022
7MichelleRandallF43
5StevenWebberM8011
1JohnSmithM10022
9RobertPickeringM10022
3TomCapperM32043
8AndrewCooperM40054
4MarkHollowayM41065

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

Get Your SQL Cheat Sheets Now: