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 :

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 :

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_Rankdoes 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.

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 :

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.

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…!!!