RANK function
The RANK function is an OLAP ranking function that calculates a ranking value for each row in an OLAP window. The return value is an ordinal number, which is based on the required ORDER BY expression in the OVER clause.
Syntax
Usage
The rank of a row is defined as 1 plus the number of rows whose rankings precede that of the row. If two or more rows have the same value, these rows get the same rank as well. Results can have a gap in the sequence of consecutive ranked values. For example, if two rows are ranked 1, the next ranking is 3. The DENSE_RANK function uses a different rule for ranking rows that include non-unique values.
The RANK function takes no argument, but the empty parentheses must be specified. If the OVER clause specifies the optional window PARTITION clause, the rankings are calculated within the subset of rows that each partition defines.
Example: RANK function
The following query ranks sales people by the amount of their sales. The ranks are not consecutive because ties in sales amounts are both assigned the same rank value, and the next rank value is skipped.
SELECT emp_num, sales,
RANK() OVER (ORDER BY sales) AS rank
FROM sales;
emp_num sales rank
101 2,000 1
102 2,400 2
103 2,400 2
104 2,500 4
105 2,500 4
106 2,650 6