Dense Rank function in SQL Server


In my previous article, we discussed the concept of Rank function in SQL server. Moving on the same concept, we will now discuss the concept of Dense_Rank() function. For better understanding of this function, I would recommend you to go through my very first article of the series here, as it explains the basics of the syntax of the ranking functions. For this discussion, we will be adding some more records to data of our previous discussions, and our data setup will be like following :

Rank function in SQL Server

Again, we will be discussing the concept with two cases :

Case 1 – Ranking entire result set using Order By clause 

Our entire query will remain same except the ranking function is changed to Dense_Rank in this discussion :

SELECT Dense_Rank() OVER (ORDER BY DepartmentName DESC) AS GeneratedRank, MemberName, Gender, DepartmentName FROM dbo.DepartmentMembers

Execute the query and see the results.

Rank function in SQL Server

In our previous discussion of Rank function, we saw that case 1 resulted in assigning the same ranking or numbering to the records, having same value in the column, on which we added the order by clause. Same is the case here. But in this case, the difference is, that Dense_Rank does not skip any rank or numbering when it moves from one set of Department to another. Had we used the Rank function here, it would have assigned rank 3 to Ben, Chris and Alice and then for Greg, it would have started from

2 (Management type) + 3 (HR type) + 1 = 6

And same 6 rank will get assigned to the rest of the records in the Business Department. Let’s a try and see the results.

Dense_Rank() in SQL Server

That’s what we discussed.

Case 2 – Ranking partitioned result set using Partition By Clause

The query remains the same, except the Partition By clause also gets added. So the query changes to :

SELECT DENSE_RANK() OVER (PARTITION BY Gender ORDER BY DepartmentName DESC) AS GeneratedRank, MemberName, Gender, DepartmentName FROM dbo.DepartmentMembers

Execute the query and see the results.

Rank function in SQL Server

This time, it first generates the partition by the Gender column (as specified by the partition by clause), then orders the results by the DepartmentName within each partitioned result set and finally implements the ranking, based on the logic we discussed above in case 1. But the concept remains the same that there was no skipping of the rank or numbering in the data, as it would have been in the case of Rank() function..

Had we used the Rank function, it would have assigned Rank 3 to Alice (2 Management type + 1 = 3) in the result set 1 and 3 for Jay and Greg (2 HR type + 1 = 3) and also would have skipped the numbering accordingly. Let’s change the query and see the results.

Dense_Rank() in SQL Server

So this was about the concept of Dense_Rank function in SQL server. In my next article, we will discuss the concept of NTile function. Hope you enjoyed reading it…!!!

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged , . Bookmark the permalink.

I have a suggestion..

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s