DENSE_RANK (Transact-SQL)

Άρθρο
09/03/2024

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

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

Transact-SQL syntax conventions

Syntax

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Arguments

<partition_by_clause>
First divides the result set produced by the FROM clause into partitions, and then the DENSE_RANK function is applied to each partition. See OVER Clause (Transact-SQL) for the PARTITION BY syntax.

<order_by_clause>
Determines the order in which the DENSE_RANK function applies to the rows in a partition.

Return Types

bigint

Remarks

If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank. For example, if the two top salespeople have the same SalesYTD value, they will both have a rank value of one. The salesperson with the next highest SalesYTD will have a rank value of two. This exceeds the number of distinct rows that come before the row in question by one. Therefore, the numbers returned by the DENSE_RANK function do not have gaps, and always have consecutive rank values.

The sort order used for the whole query determines the order of the rows in the result set. This implies that a row ranked number one does not have to be the first row in the partition.

DENSE_RANK is nondeterministic. See Deterministic and Nondeterministic Functions for more information.

Examples

A. Ranking rows within a partition

This example ranks the products in inventory, by the specified inventory locations, according to their quantities. DENSE_RANK partitions the result set by LocationID and logically orders the result set by Quantity. Notice that products 494 and 495 have the same quantity. Because they both have the same quantity value, they both have a rank value of one.

USE AdventureWorks2022;  
GO  
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  
    ,DENSE_RANK() OVER   
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank  
FROM Production.ProductInventory AS i   
INNER JOIN Production.Product AS p   
    ON i.ProductID = p.ProductID  
WHERE i.LocationID BETWEEN 3 AND 4  
ORDER BY i.LocationID;  
GO

Here's the result set.

ProductID   Name                               LocationID Quantity Rank  
----------- ---------------------------------- ---------- -------- -----  
494         Paint - Silver                     3          49       1  
495         Paint - Blue                       3          49       1  
493         Paint - Red                        3          41       2  
496         Paint - Yellow                     3          30       3  
492         Paint - Black                      3          17       4  
495         Paint - Blue                       4          35       1  
496         Paint - Yellow                     4          25       2  
493         Paint - Red                        4          24       3  
492         Paint - Black                      4          14       4  
494         Paint - Silver                     4          12       5  
  
(10 row(s) affected)

B. Ranking all rows in a result set

This example returns the top ten employees ranked by their salary. Because the SELECT statement did not specify a PARTITION BY clause, the DENSE_RANK function applied to all result set rows.

USE AdventureWorks2022;  
GO  
SELECT TOP(10) BusinessEntityID, Rate,   
       DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  
FROM HumanResources.EmployeePayHistory;

Here's the result set.

BusinessEntityID Rate                  RankBySalary  
---------------- --------------------- --------------------  
1                125.50                1  
25               84.1346               2  
273              72.1154               3  
2                63.4615               4  
234              60.0962               5  
263              50.4808               6  
7                50.4808               6  
234              48.5577               7  
285              48.101                8  
274              48.101                8

C. Four ranking functions used in the same query

This example shows the four ranking functions

used in the same query. See each ranking function for function-specific examples.

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's 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

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

D: Ranking rows within a partition

This example ranks the sales representatives in each sales territory according to their total sales. DENSE_RANK partitions the rowset by SalesTerritoryGroup, and sorts the result set by SalesAmountQuota.

-- Uses AdventureWorks  
  
SELECT LastName, SUM(SalesAmountQuota) AS TotalSales, SalesTerritoryGroup,  
    DENSE_RANK() OVER (PARTITION BY SalesTerritoryGroup ORDER BY SUM(SalesAmountQuota) DESC ) AS RankResult  
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey  
INNER JOIN dbo.DimSalesTerritory AS st ON e.SalesTerritoryKey = st.SalesTerritoryKey  
WHERE SalesPersonFlag = 1 AND SalesTerritoryGroup != N'NA'  
GROUP BY LastName, SalesTerritoryGroup;

Here's the result set.

 LastName          TotalSales     SalesTerritoryGroup  RankResult  
----------------  -------------  -------------------  --------  
Pak               10514000.0000  Europe               1  
Varkey Chudukatil  5557000.0000  Europe               2  
Valdez             2287000.0000  Europe               3  
Carson            12198000.0000  North America        1  
Mitchell          11786000.0000  North America        2  
Blythe            11162000.0000  North America        3  
Reiter             8541000.0000  North America        4  
Ito                7804000.0000  North America        5  
Saraiva            7098000.0000  North America        6  
Vargas             4365000.0000  North America        7  
Campbell           4025000.0000  North America        8  
Ansman-Wolfe       3551000.0000  North America        9  
Mensa-Annan        2753000.0000  North America        10  
Tsoflias           1687000.0000  Pacific              1

Κοινή χρήση μέσω

DENSE_RANK (Transact-SQL)

Syntax

Arguments

Return Types

Remarks

Examples

A. Ranking rows within a partition

B. Ranking all rows in a result set

C. Four ranking functions used in the same query

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

D: Ranking rows within a partition

See Also

Σχόλια

Πρόσθετοι πόροι