SQL in a Nutshell, 2nd Edition

Name

DENSE_RANK( )

Synopsis

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 the case with RANK( ), gaps in rank numbers will not result from two rows sharing the same rank.

SQL2003 Syntax

DENSE_RANK( ) OVER {window_name|(window_specification)}

DB2

DB2 requires the ordering clause and does not allow the framing clause:

DENSE_RANK( ) OVER ([partitioning] ordering)

Oracle

Oracle also requires the ordering clause and does 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. The next higher row is ranked at #4. Rank numbers are not skipped, hence the term “dense.”