Ranking by Rank() function then what about Row_NUBER() TIP #37


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


enjoy !!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.