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