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 "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
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 syntax of PARTITION BY, see OVER Clause (Transact-SQL).
- < order_by_clause >
Determines the order in which the DENSE_RANK values are applied to the rows in a partition. An integer cannot represent a column in the <order_by_clause> that is used in a ranking function.
Return Types
bigint
Remarks
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
The following example returns the DENSE_RANK of the quantity of products at the various locations. Notice that the ORDER BY in the OVER clause orders the DENSE_RANK and the ORDER BY of the SELECT statement orders the result set.
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,
DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity) AS DENSE_RANK
FROM Production.ProductInventory i
JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO
See Also
Reference
RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL)
NTILE (Transact-SQL)
Ranking Functions (Transact-SQL)
Functions (Transact-SQL)