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
- Compares the
ORDER BYvalues of the preceding row and current row and ranks the current row as follows:
ORDER BYvalues 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 BYvalues are different,
DENSE_RANKincrements or decrements the current row's ranking by 1, plus the number of consecutive duplicate values in the rows that precede it.
The largest rank value is the equal to the total number of rows returned by the query.
RANK() OVER ( [ window-partition-clause ] window-order-clause )
Compared with DENSE_RANK
RANK can leave gaps in the ranking sequence, while
DENSE_RANK does not.
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 ...