Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

RANK function

You use the RANK function to return the rank of the value in the current row as compared to the value in other rows. The rank of a value reflects the order in which it would appear if the list of values was sorted.

When using the RANK function, the rank is calculated for the expression specified in the window's ORDER BY clause. If the ORDER BY clause includes multiple expressions, the second and subsequent expressions are used to break ties if the first expression has the same value in adjacent rows. NULL values are sorted before any other value (in ascending sequence).

The following query determines the three most expensive products in the database. A descending sort sequence is specified for the window so that the most expensive products have the lowest rank, that is, rankings start at 1.

SELECT Top 3 *
       FROM ( SELECT Description, Quantity, UnitPrice,
              RANK() OVER ( ORDER BY UnitPrice DESC ) AS Rank 
              FROM Products ) AS DT
ORDER BY Rank;

This query returns the following result:

Note that rows 1 and 2 have the same value for Unit Price, and therefore also have the same rank. This is called a tie.

With the RANK function, the rank value jumps after a tie. For example, the rank value for row 3 has jumped to three instead of 2. This is different from the DENSE_RANK function, where no jumping occurs after a tie. See DENSE_RANK function.

The following SQL query finds the male and female employees from Utah and ranks them in descending order according to salary.

SELECT Surname, Salary, Sex,
     RANK() OVER ( ORDER BY Salary DESC ) "Rank"
     FROM Employees 
WHERE State IN ( 'UT' );

The table that follows represents the result set from the query:

You can partition your data to provide different results. Using the query from Example 2, you can change the data by partitioning it by gender. The following example ranks employees in descending order by salary and partitions by gender.

SELECT Surname, Salary, Sex,
     RANK () OVER ( PARTITION BY Sex
     ORDER BY Salary DESC ) "Rank"
     FROM Employees 
WHERE State IN ( 'UT' );

The table that follows represents the result set from the query:

For more information on the syntax for the RANK function, see RANK function [Ranking].