# Oracle/PLSQL: DENSE_RANK Function

Learn how to use the Oracle/PLSQL **DENSE_RANK function** with syntax and examples.

## Description

The Oracle/PLSQL **DENSE_RANK function** returns the rank of a row in a group of rows. It is very similar to the RANK function. However, the RANK function can cause non-consecutive rankings if the tested values are the same. Whereas, the DENSE_RANK** function** will always result in consecutive rankings.

The DENSE_RANK function can be used two ways - as an Aggregate function or as an Analytic function.

## DENSE_RANK Function Syntax #1 - Used as an Aggregate Function

As an Aggregate function, the **DENSE_RANK function** returns the dense rank of a row within a group of rows.

The syntax for the **DENSE_RANK function** when used as an Aggregate function is:

DENSE_RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )

### Parameters or Arguments

*expression1 .. expression_n* can be one or more expressions which identify a unique row in the group.

## Note

- There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.
- The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

## Applies To

The DENSE_RANK function can be used in the following versions of Oracle/PLSQL:

- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

## Example (as an Aggregate Function)

Let's look at some Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL.

For example:

select DENSE_RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;

The SQL statement above would return the dense rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

## DENSE_RANK Function Syntax #2 - Used as an Analytic Function

As an Analytic function, the DENSE_RANK function returns the rank of each row of a query with respective to the other rows.

The syntax for the DENSE_RANK function when used as an Analytic function is:

DENSE_RANK() OVER ( [ query_partition_clause] ORDER BY clause )

## Applies To

The DENSE_RANK function can be used in the following versions of Oracle/PLSQL:

- Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

## Example (as an Analytic Function)

Let's look at some Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL.

For example:

select employee_name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing';

The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the DENSE_RANK function would return the same rank for both employees.