Rank() and DENSE_RANK() functions are used to return the RANK starting from 1 based on the ordering clause. The RANK of row is one plus number of rows before it.Rank function skips the RANKING if there is a tie eg: 1,2,2,4,5. So here 3 is skipped due to tie in 2,2 so they both tied row will return rank as 2 and after that 4 will be returned . DENSE_RANK is also used to RANK starting from 1. The major difference between RANK() and DENSE_RANK functon is that RANK function skips the RANK is there is tie while DENSE_RANK returns the RANK without any gap.

Syntax

RANK()  OVER  ([partion by clause is optional]  ORDER BY COL1, COL2, …)

DENSE_RANK()  OVER  ( [partion by clause is optional] ORDER BY COL1, COL2, …)

Let’t take an example of below mentioned table which consist of Cricket player records for the season.

Now in order demonstrate to RANK the player according to their Run scored, we will makes use of RANK and DENSE_RANK function to get the difference between them, as shown below:

select NAME,TOTALSCORE,
RANK() OVER (ORDER BY TOTALSCORE DESC) [RANK],
DENSE_RANK() OVER (ORDER BY TOTALSCORE DESC) [DENSERANK]
FROM PLAYER

We can clearly see the difference between RANK and DENSE_RANK() once there is a tie RANK() function skips the RANK number while DENSE_RANK() returns the rank number.