Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
RANK (Transact-SQL)
Collapse the table of content
Expand the table of content

RANK (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions


RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. The order_by_clause is required. The <rows or range clause> of the OVER clause cannot be specified for the RANK function. For more information, see OVER Clause (Transact-SQL).

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.

RANK is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.

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
    ,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       3
496         Paint - Yellow         3            30       4
492         Paint - Black          3            17       5
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)

The following example returns the top ten employees ranked by their salary. Because a PARTITION BY clause was not specified, the RANK function was applied to all rows in the result set.

USE AdventureWorks2012
SELECT TOP(10) BusinessEntityID, Rate, 
       RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory AS eph1
WHERE RateChangeDate = (SELECT MAX(RateChangeDate) 
                        FROM HumanResources.EmployeePayHistory AS eph2
                        WHERE eph1.BusinessEntityID = eph2.BusinessEntityID)
ORDER BY BusinessEntityID;

Here is the result set.

BusinessEntityID Rate                  RankBySalary
---------------- --------------------- --------------------
1                125.50                1
2                63.4615               4
3                43.2692               8
4                29.8462               19
5                32.6923               16
6                32.6923               16
7                50.4808               6
8                40.8654               10
9                40.8654               10
10               42.4808               9

Community Additions

ADD
Show:
© 2015 Microsoft