# ROW_NUMBER

Returns an ascending sequence of integers, starting with 1. Starts the sequence over for each group produced by the `PARTITIONED BY` clause. The output sequence includes different values for duplicate input values. Therefore, the sequence never contains any duplicates or gaps, regardless of duplicate input values.

Syntax:

``ROW_NUMBER() OVER([partition_by_clause] order_by_clause)``

The `ORDER BY` clause is required. The `PARTITION BY` clause is optional. The window clause is not allowed.

Usage notes:

Often used for top-N and bottom-N queries where the input values are known to be unique, or precisely N rows are needed regardless of duplicate values.

Because its result value is different for each row in the result set (when used without a `PARTITION BY` clause), `ROW_NUMBER()` can be used to synthesize unique numeric ID values, for example for result sets involving unique values or tuples.

Similar to `RANK` and `DENSE_RANK`. These functions differ in how they treat duplicate combinations of values.

Examples:

The following example demonstrates how `ROW_NUMBER()` produces a continuous numeric sequence, even though some values of `X` are repeated.

``````select x, row_number() over(order by x, property) as row_number, property from int_t;
+----+------------+----------+
| x  | row_number | property |
+----+------------+----------+
| 1  | 1          | odd      |
| 1  | 2          | square   |
| 2  | 3          | even     |
| 2  | 4          | prime    |
| 3  | 5          | odd      |
| 3  | 6          | prime    |
| 4  | 7          | even     |
| 4  | 8          | square   |
| 5  | 9          | odd      |
| 5  | 10         | prime    |
| 6  | 11         | even     |
| 6  | 12         | perfect  |
| 7  | 13         | lucky    |
| 7  | 14         | lucky    |
| 7  | 15         | lucky    |
| 7  | 16         | odd      |
| 7  | 17         | prime    |
| 8  | 18         | even     |
| 9  | 19         | odd      |
| 9  | 20         | square   |
| 10 | 21         | even     |
| 10 | 22         | round    |
+----+------------+----------+
``````

The following example shows how a financial institution might assign customer IDs to some of history's wealthiest figures. Although two of the people have identical net worth figures, unique IDs are required for this purpose. `ROW_NUMBER()` produces a sequence of five different values for the five input rows.

``````select row_number() over (order by net_worth desc) as account_id, name, net_worth
from wealth order by account_id, name;
+------------+---------+---------------+
| account_id | name    | net_worth     |
+------------+---------+---------------+
| 1          | Solomon | 2000000000.00 |
| 2          | Croesus | 1000000000.00 |
| 3          | Midas   | 1000000000.00 |
| 4          | Crassus | 500000000.00  |
| 5          | Scrooge | 80000000.00   |
+------------+---------+---------------+
``````