Ranking Function in SQL server

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic. This is a window function.

Ranking Function likes the ROW_NUMBER () function except for the rows with equal values, where it will rank with the same rank ID and generate a gap after it.

Syntax:

RANK() OVER([PARTITION BY <col1>[,<col2>,...<n>]] ORDER BY <col3>[,<col4>])

Over and Order by clause are mandatory and Partition by clause is an option. As per our requirement, we will it.

See the example without partition by clause

Here I am using “AdventureWorks2012” Database for the demo.

SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd') AS OrderDate, SalesOrderID, RANK() OVER(ORDER BY CustomerID) AS Rank_Number FROM Sales.SalesOrderHeader;

See the result.

   

 

See the example with partition by clause

SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd') AS OrderDate, SalesOrderID, RANK() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS Rank_Number FROM Sales.SalesOrderHeader;

   

It will return the rank based on the customer ID.