Ranking Functions (Transact-SQL)

 

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.

Transact-SQL provides the following ranking functions:

RANKNTILE
DENSE_RANKROW_NUMBER

The following shows the four ranking functions used in the same query. For function specific examples, see each ranking function.

USE AdventureWorks2012;  
GO  
SELECT p.FirstName, p.LastName  
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"  
    ,RANK() OVER (ORDER BY a.PostalCode) AS Rank  
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile  
    ,s.SalesYTD  
    ,a.PostalCode  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;  

Here is the result set.

FirstNameLastNameRow NumberRankDense RankQuartileSalesYTDPostalCode
MichaelBlythe11114557045.045998027
LindaMitchell21115200475.231398027
JillianCarson31113857163.633298027
GarrettVargas41111764938.985998027
TsviReiter51122811012.715198027
ShuIto66223018725.485898055
JoséSaraiva76223189356.246598055
DavidCampbell86233587378.425798055
TeteMensa-Annan96231931620.183598055
LynnTsoflias106231758385.92698055
RachelValdez116242241204.042498055
JaePak126245015682.375298055
RanjitVarkey Chudukatil136243827950.23898055

Built-in Functions (Transact-SQL)
OVER Clause (Transact-SQL)

Community Additions

ADD
Show: