DENSE_RANK (Transact-SQL)
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.
.gif "Ikona łącza do tematu")
Transact-SQL Syntax Conventions
Składnia
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Arguments
<partition_by_clause>
Divides the result set produced by the FROM clause into partitions to which the DENSE_RANK function is applied. For the PARTITION BY syntax, see OVER Clause (Transact-SQL).<order_by_clause>
Determines the order in which the DENSE_RANK function is applied to the rows in a partition.
Return Types
bigint
Uwagi
If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition.
Examples
A. Ranking rows within a partition
The following example ranks the products in inventory the specified inventory locations according to their quantities. The result set is partitioned by LocationID and logically ordered by Quantity. Notice that products 494 and 495 have the same quantity. Because they are tied, they are both ranked one.
USE AdventureWorks2012;
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 is 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
The following example returns the top ten employees ranked by their salary. Because a PARTITION BY clause was not specified, the DENSE_RANK function was applied to all rows in the result set.
USE AdventureWorks2012;
GO
SELECT TOP(10) BusinessEntityID, Rate,
DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory;
Here is 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