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.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.
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 ...