SQL RANK vs DENSE_RANK top usage

Rank Function 

• Rank function assigns the same rank and skips one rank

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

Related: Best SQL Course for Beginner to Professional 

Table 1. Example of values returned when you specify RANK. Rank 3 skipped, and it assigns rank 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

• Dense_rank function assigns same rank for duplicates and continue ranking

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

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 in a good online course provider. Then your knowledge on writing SQL queries will improve much better. If you are interested you can try learning SQL here.