SQL in a Nutshell, 3rd Edition

Name

DENSE_RANK

Assigns a rank to each row in a partition, which should be ordered in some manner. The rank for a given row is computed by counting the number of rows preceding the row in question and then adding 1 to the result. Rows with duplicate ORDER BY values will rank the same. Unlike with RANK, gaps in rank numbers will not result from two rows sharing the same rank.

ANSI SQL Standard Syntax

DENSE_RANK(  ) OVER {window_name | (window_specification)}

Oracle and SQL Server

Oracle and SQL Server also require the ordering clause and do not allow the framing clause:

DENSE_RANK(  ) OVER ([partitioning] ordering)

Example

Compare the results from the following Oracle-based example to those shown in the section on the RANK function:

SELECT NUM, DENSE_RANK(  ) OVER (ORDER BY NUM) rank
FROM test4;
       NUM       RANK
---------- ----------
         0          1
         1          2
         2          3
         3          4
         3          4
         4          5
         5          6

The two rows where NUM=3 are both ranked at #3, and the next-higher row is ranked at #4. Rank numbers are not skipped, hence the term “dense.”