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)
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho