RANK function [Ranking]

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
Returns

INTEGER

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
Standards and compatibility
  • SQL/2003   SQL/OLAP feature T612.

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');
Surname Salary State Rank
Shishov 72995.000 UT 1
Wang 68400.000 UT 2
Cobb 62000.000 UT 3
Morris 61300.000 UT 4
Davidson 57090.000 NY 5
Martel 55700.000 NY 6
Blaikie 54900.000 NY 7
Diaz 54900.000 NY 7
Driscoll 48023.690 UT 9
Hildebrand 45829.000 UT 10
Whitney 45700.000 NY 11
... ... ... ...
Lynch 24903.000 UT 19