Ranking Functions in SQL Server

 SQL Server Ranking Function:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• NTILE()

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.

Click on the link above to see each ranking function with example.

Below example demonstrates the four ranking functions in one go.

Example:

SelesPerson Table

FirstName	LastName	SalesYTD	PostalCode
Michael	Blythe	3763178.179	98027
Linda	Mitchell	4251368.55	98027
Jillian	Carson	3189418.366	98027
Garrett	Vargas	1453719.465	98027
Tsvi	Reiter	2315185.611	98027
Pamela	Ansman-Wolfe	1352577.133	98027
Shu	Ito	2458535.617	98055
José	Saraiva	2604540.717	98055
David	Campbell	1573012.938	98055
Tete	Mensa-Annan	1576562.197	98055
Lynn	Tsoflias	1421810.924	98055
Rachel	Valdez	1827066.712	98055
Jae	Pak	4116871.228	98055
Ranjit	Varkey Chudukatil	3121616.32	98055

SELECT FirstName, LastName  
    ,ROW_NUMBER() OVER (ORDER BY PostalCode) AS "Row Number"  
    ,RANK() OVER (ORDER BY PostalCode) AS Rank  
    ,DENSE_RANK() OVER (ORDER BY PostalCode) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY PostalCode) AS Quartile  
    ,SalesYTD  
    ,PostalCode  
FROM SalesPerson

Output:

FirstName	LastName	Row Number	Rank	Dense Rank	Quartile	SalesYTD	PostalCode
Michael	Blythe	1	1	1	1	3763178	98027
Linda	Mitchell	2	1	1	1	4251369	98027
Jillian	Carson	3	1	1	1	3189418	98027
Garrett	Vargas	4	1	1	1	1453719	98027
Tsvi	Reiter	5	1	1	2	2315186	98027
Pamela	Ansman-Wolfe	6	1	1	2	1352577	98027
Shu	Ito	7	7	2	2	2458536	98055
José	Saraiva	8	7	2	2	2604541	98055
David	Campbell	9	7	2	3	1573013	98055
Tete	Mensa-Annan	10	7	2	3	1576562	98055
Lynn	Tsoflias	11	7	2	3	1421811	98055
Rachel	Valdez	12	7	2	4	1827067	98055
Jae	Pak	13	7	2	4	4116871	98055
Ranjit	Varkey Chudukatil	14	7	2	4	3121616	98055

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