Skip to content

ranking-functions-transact-sql.md

Latest commit

 

History

History
88 lines (78 loc) · 3.29 KB

ranking-functions-transact-sql.md

File metadata and controls

88 lines (78 loc) · 3.29 KB
title Ranking Functions (Transact-SQL)
description Ranking Functions (Transact-SQL)
author MikeRayMSFT
ms.author mikeray
ms.date 03/15/2017
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
helpviewer_keywords
ranking functions
row ranking [SQL Server]
functions [SQL Server], ranking
ranking rows
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

Ranking Functions (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

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.

[!INCLUDEtsql] provides the following ranking functions:

:::row::: :::column::: RANK :::column-end::: :::column::: NTILE :::column-end::: :::row-end:::
:::row::: :::column::: DENSE_RANK :::column-end::: :::column::: ROW_NUMBER :::column-end::: :::row-end:::

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;

[!INCLUDEssResult]

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)