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.

- 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*