DB Software v7.8

ROW_NUMBER

Ranking function. Returns the number of the current row within its partition.

Syntax

ROW_NUMBER () OVER (
    [PARTITION BY (col | expr), ...]
    [ORDER BY (col | expr), ...]
)

Return Type

An integer

Examples

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 |
+------+------+---------------------------------------------------+
In this section: