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:

1. Sorts partition rows as specified by the ORDER BY clause.
2. 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.

The largest rank value is the equal to the total number of rows returned by the query.

Behavior Type

Immutable

Syntax

RANK() OVER(
... [ window-partition-clause ]
... window-order-clause )

Parameters

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