Rank Function in SQL Server

My previous article described the Row_Number ranking function in SQL Server. Moving on with the same concept, this article explains the Rank function. For a better understanding of this function, I recommend you go through the previous article here, since it explains the basics of the syntax of the ranking functions. And for your convenience, the following is the data setup we had in the previous discussion.



The Rank function

The Rank() function is just another ranking function in SQL Server. The main point is that, while assigning ranks, it assigns the same rank to records of a result set, that have the same value (of the column, on which the order by clause is applied) and for the further ranking of the records, it skips the number of records that have been already assigned to the records in the previous result set.

For example, if we have say 10 records, with first 4 having the same value in a column, then all of them will be assigned the same rank 1. For the next record, for example the 5th record, the ranking will start from 5, instead of 2. This will become clearer with the following example. As in the previous discussion, we will again be discussing the concept with two cases.

Case 1: Ranking an entire result set using Order By clause

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

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

Execute the query and see the results.

The difference between this output and the output of the Row_Number is that, in Row_Number(), each row is assigned a unique number, irrespective of the column on which it is applied and also the value of that column, on which the rank is applied by the order clause. But in this case, it assigns the same ranking or numbering to the records that have the same value in the column specified in the order by clause. Further, assignment of the ranks starts as the number of records in the previous set + 1. In other words, it implicitly implements grouping on the column specified in the order by clause and skips the numbers until the next group starts and then further starts the numbering from the previous record set count + 1.

For example, in the preceding example, it implicitly groups the departments and assigns the same rank 1 to all the records of the HR department. Further rank assignment starts from the number of records of the HR department + 1 which is 3 + 1 = 4.

Similarly, if we had more records in the table then after the Business Development department, its ranking would start from 3 (HR type) + 4 (Business Development type) + 1 = 8.

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 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 and finally implements the ranking, based on the logic we discussed above. Here, our result set is divided into two groups by the gender column.

In the first set, it assigns the rank 1 to Alice. Next Kary and Jenny are assigned rank 2, since they belong to the same department, Business Development. Had Kary also belonged to the HR department, it would have also received the rank 1. In that case, Jenny would have received the rank as 2 (HR type) + 1 = 3. Change the data for Kary to HR and try it.

In the second set, it does what was expected. Assigning the same rank 1 to the first 2 records and starting the rank for the next record set from 2 (HR type) + 1 = 3.

So this was about the the Rank function in SQL Server. The scripts related to data setup and queries are also attached for reference.