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