SQL Window Functions: RANK vs DENSE_RANK Top Usage

Rank Function

Returns 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
2:37:26

Table 1. Example of values returned when you specify RANK. Rank 3 was skipped, and it was assigned rank 5.

Value	Rank number
2:31:57	1
2:34:52	2
2:34:52	2
2:37:26	4
2:38:01	5

Data for RANK function:

DATA
-------
100
 35
 23
  8
  8
  6

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;

SQL returns the following ranked data:

DATA                  RANK_DATA
----------                 ---------
 100                1
  35                2
  23                3
   8                4
   8                4
   6                6

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

DENSE_RANK Function

Returns 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 DENSE_RANK

Value	Rank number
2:31:57	1
2:34:52	2
2:34:52	2
2:37:26	3
2:38:01	4

SQL Query for 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;

SQL returns the following ranked data:

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

In the above 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.

Summary of RANK and DENSE_RANK

The recommendation is to learn SQL from a good online course provider. Then your knowledge of writing SQL queries will improve much better. If you are interested you can try learning SQL here.

Srini

Data Engineer with deep AI and Generative AI expertise, crafting high-performance data pipelines in PySpark, Databricks, and SQL. Skilled in Python, AWS, and Linux—building scalable, cloud-native solutions for smart applications.