RANK [Analytic]
Within each window partition, ranks all rows in the query results set according to the order specified by the window's ORDER BY
clause.
RANK
executes as follows:
- Sorts partition rows as specified by the
ORDER BY
clause. - Compares the
ORDER BY
values of the preceding row and current row and ranks the current row as follows:- If
ORDER BY
values are the same, the current row gets the same ranking as the preceding row.Note: Null values are considered equal. For detailed information on how null values are sorted, see NULL Sort Order.
- If the
ORDER BY
values are different,DENSE_RANK
increments or decrements the current row's ranking by 1, plus the number of consecutive duplicate values in the rows that precede it.
- If
The largest rank value is the equal to the total number of rows returned by the query.
Behavior Type
Syntax
RANK() OVER( ... [ window-partition-clause ] ... window-order-clause )
Parameters
OVER()
|
Compared with DENSE_RANK
RANK
can leave gaps in the ranking sequence, while DENSE_RANK
does not. For more information, see DENSE_RANK
.
Examples
The following query ranks by state all company customers that have been customers since 2007. In rows where the customer_since
dates are the same, RANK
assigns the rows equal ranking. When the customer_since
date changes, RANK
skips one or more rankings—for example, within CA
, from 12 to 14, and from 17 to 19.
=> SELECT customer_state, customer_name, customer_since, RANK() OVER (PARTITION BY customer_state ORDER BY customer_since) AS rank FROM customer_dimension WHERE customer_type='Company' AND customer_since > '01/01/2007' ORDER BY customer_state; customer_state | customer_name | customer_since | rank ----------------+---------------+----------------+------ AZ | Foodshop | 2007-01-20 | 1 AZ | Goldstar | 2007-08-11 | 2 CA | Metahope | 2007-01-05 | 1 CA | Foodgen | 2007-02-05 | 2 CA | Infohope | 2007-02-09 | 3 CA | Foodcom | 2007-02-19 | 4 CA | Amerihope | 2007-02-22 | 5 CA | Infostar | 2007-03-05 | 6 CA | Intracare | 2007-03-14 | 7 CA | Infocare | 2007-04-07 | 8 ... CO | Goldtech | 2007-02-19 | 1 CT | Foodmedia | 2007-02-11 | 1 CT | Metatech | 2007-02-20 | 2 CT | Infocorp | 2007-04-10 | 3 ...