RANK AND DENSE_RANK METHOD IN SQL SERVER TO RANK THE PLAYERS

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.

1

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

2

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.