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.
