Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

DENSE_RANK function

Syntax

DENSE_RANK() OVER ( [PARTITION BY value_expression, ... [n]] order_by_clause;

Description

DENSE_RANK returns a temporary value calculated when the query is run. It returns the rank of a row within a partition of a result set. The rank is one plus the number of ranks that come before the row in question with no gaps in the ranking values.

Example

Given the following table definition:

create table ranking (c1 int, c2 int);

insert into ranking values (1,1);

insert into ranking values (1,2);

insert into ranking values (1,2);

insert into ranking values (1,3);

insert into ranking values (2,1);

insert into ranking values (2,1);

insert into ranking values (2,2);

insert into ranking values (2,3);

commit work;

select c1, c2, dense_rank() over (partition by c1 order by c2) from ranking;



C1 C2 DENSE_RANK()

-- -- ------------

1 1 1

1 2 2

1 2 2

1 3 3

2 1 1

2 1 1

2 2 2

2 3 3

Notes

  • DENSE_RANK ensures consecutive values for all result sets with no gaps in numbering. Use DENSE_RANK for "tied" rank numbering.
  • The PARTITION BY clause distributes the rows in the result set into partitions by one or more value_expression.
  • Only a single ranking function may be used in a statement.
  • Ranking functions are nondeterministic as they are computed for each generated result set.
  • Introduced in V12.0.1

See Also

TOCIndex