# 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 |
+------------+---------+---------------+
```