Dense Rank Function in SQL Server

My previous article explained the Rank function in SQL Server. Moving on with the same concept, we will now discuss the Dense_Rank() function. For a better understanding of this function, I recommend you go through my very first article of the series here, since it explains the basics of the syntax of the ranking functions. For this discussion, we will be adding some more records to the data of our previous discussions and our data setup will be like the 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 the 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.



In our previous discussion of the Rank function, we saw that case 1 resulted in assigning the same ranking or numbering to the records, having the same value in the column, on which we added the order by clause. The 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 Departments 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 the same 6 rank will be assigned to the rest of the records in the Business Department. Let's try it 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 is also 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.



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 the 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 Dense_Rank function in SQL Server. I hope you enjoyed reading it.