SQL Server RANK() Function with example

 Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

Syntax:

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

Example:

Table: SalesPerson

FirstName	LastName	TerritoryName
Lynn	Tsoflias	Australia
José	Saraiva	Canada
Garrett	Vargas	Canada
Jillian	Carson	Central
Ranjit	Varkey	France
Rachel	Valdez	Germany
Michael	Blythe	Northeast
Tete	Mensa-Annan	Northwest
David	Campbell	Northwest
Pamela	Ansman-Wolfe	Northwest
Tsvi	Reiter	Southeast
Linda	Mitchell	Southwest
Shu	Ito	Southwest
Jae	Pak	United Kingdom

Returning Row Number:

SELECT RANK() OVER(ORDER BY TerritoryName) AS Row,   
    FirstName, LastName,TerritoryName
FROM SalesPerson

Output:

ROW	FirstName	LastName	TerritoryName
1	Lynn	Tsoflias	Australia
2	Garrett	Vargas	Canada
2	José	Saraiva	Canada
4	Jillian	Carson	Central
5	Ranjit	Varkey	France
6	Rachel	Valdez	Germany
7	Michael	Blythe	Northeast
7	Tete	Mensa-Annan	Northwest
7	David	Campbell	Northwest
7	Pamela	Ansman-Wolfe	Northwest
11	Tsvi	Reiter	Southeast
11	Linda	Mitchell	Southwest
11	Shu	Ito	Southwest
14	Jae	Pak	United Kingdom

Using ROW_NUMBER() with PARTITION

USE AdventureWorks2017;
GO  
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  
    ,RANK() OVER   
    (PARTITION BY i.LocationID ORDER BY i.Quantity) AS Rank  
FROM Production.ProductInventory AS i   
INNER JOIN Production.Product AS p   
    ON i.ProductID = p.ProductID  
WHERE i.LocationID BETWEEN 3 AND 4  
ORDER BY i.LocationID; 

Output:

ProductID	Name	LocationID	Quantity	Rank
492	Paint - Black	3	17	1
496	Paint - Yellow	3	30	2
493	Paint - Red	3	41	3
494	Paint - Silver	3	49	4
495	Paint - Blue	3	49	4
494	Paint - Silver	4	12	1
492	Paint - Black	4	14	2
493	Paint - Red	4	24	3
496	Paint - Yellow	4	25	4
495	Paint - Blue	4	35	5