In my previous article, we discussed the concept of Row_Number ranking function in SQL server. Moving on the same concept, we will now discuss the concept of Rank function. For better understanding of this function, I would recommend you to go through the previous article here, as it explains the basics of the syntax of the ranking functions. And for the ease of the users, following is the data setup we had in the previous discussion.
What is Rank function?
Rank() function is just another ranking function in sql server. The main point is that, while assigning ranks, it assigns same rank to records of a result set, which 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 which have been already assigned to the records in the previous result set.
For example, if we have say 10 records, with first 4 having same value in a column, then all of them will be assigned the same rank 1. For the next record i.e. 5th record, ranking will start from the 5, instead of 2. This will become clearer with the following example. Like in the previous discussion, 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 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 value of that column, on which the rank is applied by the order clause. But in this case, it assigns SAME ranking or numbering to the records, which have same value, in the column specified in the order by clause. Further assignment of the ranks starts as 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 previous record set count + 1.
For example, in the above example, it implicitly groups the departments and assigns same rank 1 to all the records of the HR department. Further rank assignment starts from number of records of HR department + 1 which is 3 + 1 = 4.
Similarly, if we had more records in the table, after 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 also gets 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 first set, it assigns the rank 1 to Alice. Next Kary and Jenny are assigned rank 2, as 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 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 concept of Rank function in SQL server. In next article, we will discuss the concept of Dense_Rank().