In last post we have discussed Row_Number() function. In similar way we have RANK function. By the name it is clear that we provide some kind of ranking for rows.
It syntax is exact same like ROW_NUMBER().
Now the question comes in mind if it is same then why SQL SERVER introduce new function.
so there is slightly difference
ROW_NUMBER() will generate sequential number regardless of duplicate rows in partition.
While RANK() will generate sequential number for unique row in partition and use same sequence for duplicate and skip sequence which lies between duplicate.
For example in below query we want to know total dues rank according to territory and modified date.
so we write following
SELECT TerritoryID,TotalDue,ModifiedDate,SalesOrderNumber ,RANK() OVER (Partition by ModifiedDate,TerritoryID ORDER BY TotalDue) ranks FROM [Sales].[SalesOrderHeader]
When we run it we get following result