RANK( ) OVER ( window-spec )

window-spec : see the Remarks section below

INTEGER

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.

For more information about specifying a window specification in an OVER clause, see Window definition: inlining using the OVER clause and WINDOW clause.

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

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');