Calculates the value of a rank in a group of values. In the case of ties, the RANK function leaves a gap in the ranking sequence.

Syntax

RANK( ) OVER ( window-spec )

window-spec : see the Remarks section below

Remarks

Elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. When used as a window function, you must specify an ORDER BY clause, you may specify a PARTITION BY clause, however, you can not specify a ROWS or RANGE clause. See the window-spec definition provided in WINDOW clause.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

See also

• CUME_DIST function [Ranking]
• DENSE_RANK function [Ranking]
• PERCENT_RANK function [Ranking]

Standards and compatibility

Example

The following example provides a rank in descending order of employees' salaries in Utah and New York. Notice that the 7th and 8th employees have an identical salary and therefore share the 7th place ranking. The employee that follows receives the 9th place ranking, which leaves a gap in the ranking sequence (no 8th place ranking).

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