Ranking Functions (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
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:
Examples
The following example shows the four ranking functions used in the same query. For function-specific examples, see each ranking function.
USE AdventureWorks2022;
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.
FirstName | LastName | Row Number | Rank | Dense Rank | Quartile | SalesYTD | PostalCode |
---|---|---|---|---|---|---|---|
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 |
See Also
Built-in Functions (Transact-SQL)
OVER Clause (Transact-SQL)