SQL Ranking Functions

Ranking Functions

There are four ranking functions included with SQL Server (starting with SQL Server 2005). Those functions; ROW_NUMBER, RANK, DENSE_RANK, and NTILE; can be used to rank the rows of your result set over a partition.

Row_Number

The ROW_NUMBER ranking function is the simplest of the ranking functions. Its purpose in life is to provide consecutive numbering of the rows in the result set by the order selected in the OVER clause for each partition specified in the OVER clause.

Rank

RANK – 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.

DENSE_RANK

DENSE_RANK – Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

NTILE

NTILE – Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

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. (Microsoft)

First Name	Last Name	Row Number	Rank	Dense Rank	Quartile	Sales YTD	Postal Code
Michael	Blythe	1	1	1	1	4557045.0459	98027
Linda	Mitchell	2	1	1	1	5200475.2313	98027
Jillian	Carson	3	1	1	1	3857163.6332	98027
Garrett	Vargas	4	1	1	1	1764938.9859	98027
Tsvi	Reiter	5	1	1	2	2811012.7151	98027
Shu	Ito	6	6	2	2	3018725.4858	98055
José	Saraiva	7	6	2	2	3189356.2465	98055
David	Campbell	8	6	2	3	3587378.4257	98055
Tete	Mensa-Annan	9	6	2	3	1931620.1835	98055
Lynn	Tsoflias	10	6	2	3	1758385.926	98055
Rachel	Valdez	11	6	2	4	2241204.0424	98055
Jae	Pak	12	6	2	4	5015682.3752	98055
Ranjit	Varkey Chudukatil	13	6	2	4	3827950.238	98055