Ranking the rows

You can request that Db2 calculate the ordinal rank of each row in the result set based on a particular column. For example, you can rank finishing times for a marathon to determine the first, second, and third place finishers.

Procedure

To rank rows, use one of the following ranking specifications in an SQL statement:

  • Use RANK to return a rank number for each row value.
    Use this specification if you want rank numbers to be skipped when duplicate row values exist.
    For example, suppose the top five finishers in a marathon have the following times:
    • 2:31:57
    • 2:34:52
    • 2:34:52
    • 2:37:26
    • 2:38:01
    When you use the RANK specification, Db2 returns the following rank numbers:
    Table 1. Example of values returned when you specify RANK
    Value Rank number
    2:31:57 1
    2:34:52 2
    2:34:52 2
    2:37:26 4
    2:38:01 5
  • Use DENSE_RANK to return a rank number for each row value.
    Use this specification if you do not want rank numbers to be skipped when duplicate row values exist.
    For example, when you specify DENSE_RANK with the same times that are listed in the description of RANK, Db2 returns the following rank numbers:
    Table 2. Example of values returned when you specify RANK
    Value Rank number
    2:31:57 1
    2:34:52 2
    2:34:52 2
    2:37:26 3
    2:38:01 4

Examples

Suppose that you had the following values in the DATA column of table T1:

DATA
-------
100
 35
 23
  8
  8
  6
Example: RANK

Suppose that you use the following RANK specification: 

SELECT DATA,
  RANK() OVER (ORDER BY DATA DESC) AS RANK_DATA
  FROM T1
  ORDER BY RANK_DATA;

Db2 returns the following ranked data: 

DATA		RANK_DATA
-------------------
 100				1
  35				2
  23				3
   8				4
   8				4
   6				6
Example: DENSE RANK

Suppose that you use the following DENSE_RANK specification on the same data: 

SELECT DATA,
  DENSE_RANK() OVER (ORDER BY DATA DESC) AS RANK_DATA
  FROM T1
  ORDER BY RANK_DATA;

Db2 returns the following ranked data: 

DATA		RANK_DATA
-------------------
 100				1
  36				2
  23				3
   8				4
   8				4
   6				5

In the example with the RANK specification, two equal values are both ranked as 4. The next rank number is 6. Number 5 is skipped.

In the example with the DENSE_RANK option, those two equal values are also ranked as 4. However, the next rank number is 5. With DENSE_RANK, no gaps exist in the sequential rank numbering.