RANK (Transact-SQL)
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
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 RANK function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).
- < order_by_clause >
Determines the order in which the RANK values are applied to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the < order_by_clause > is used in a ranking function.
Return Types
bigint
Remarks
If two or more rows tie for a rank, 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 three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.
The sort order that is used for the whole query determines the order in which the rows appear in a result set.
Examples
The following example ranks the products in inventory according to their quantities. The rowset is partitioned by LocationID and sorted by Quantity. Notice that the ORDER BY in the OVER clause orders the 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
,RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS 'RANK'
FROM Production.ProductInventory i
INNER JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name;
GO
See Also
Reference
DENSE_RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL)
NTILE (Transact-SQL)
Ranking Functions (Transact-SQL)
Functions (Transact-SQL)