DENSE_RANK returns ranking values without gaps.
The values for rows with ties are still equal, but the ranking of the rows represents the positions of the clusters of rows having equal values in the ordering column, rather than the positions of the individual rows. As in the RANK example, where rows ordering column values are 10, 20, 20, 20, 30, the rank of the first row is still 1 and the rank of the second row is still 2, as are the ranks of the third and fourth rows. The last row, however, is 3, not 5.
DENSE_RANK is computed through a syntax transformation, as well.
DENSE_RANK() OVER ws
is equivalent to:
COUNT ( DISTINCT ROW ( expr_1, . . ., expr_n ) ) OVER ( ws RANGE UNBOUNDED PRECEDING )
In the above example, expr_1 through expr_n represent the list of value expressions in the sort specification list of window w1.