Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
Ranking function. Returns the number of the current row within its partition.
ROW_NUMBER () OVER (
[PARTITION BY (col | expr), ...]
[ORDER BY (col | expr), ...]
)
An integer
create table example (a int, b int);
insert into example values(1,2),(2,2),(3,3),(4,3);
select a,b, row_number() over() from example;
+------+------+---------------------+
| a | b | row_number() over() |
+------+------+---------------------+
| 1 | 2 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 3 | 4 |
+------+------+---------------------+
select a,b, row_number() over(partition by b order by a desc) from example;
+------+------+---------------------------------------------------+
| a | b | row_number() over(partition by b order by a desc) |
+------+------+---------------------------------------------------+
| 2 | 2 | 1 |
| 1 | 2 | 2 |
| 4 | 3 | 1 |
| 3 | 3 | 2 |
+------+------+---------------------------------------------------+