Important

New database features are no longer connected to engine versions; features are now enabled independently during scheduled update windows, and “major” and “minor” releases no longer exist in Helios. Please visit the release notes to view the latest features available in your database clusters.

ROW_NUMBER

Syntax

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

Return Type

An integer

Examples

SQL

CREATE TABLE rownum_example (a int, b int);

INSERT INTO rownum_example VALUES(1,2),(2,2),(3,3),(4,3);

SQL

SELECT a,b, ROW_NUMBER() OVER() FROM rownum_example;

+------+------+---------------------+
| a    | b    | ROW_NUMBER() OVER() |
+------+------+---------------------+
|    3 |    3 |                   1 |
|    4 |    3 |                   2 |
|    2 |    2 |                   3 |
|    1 |    2 |                   4 |
+------+------+---------------------+

SQL

SELECT a,b, ROW_NUMBER() OVER(PARTITION BY b ORDER BY a DESC) FROM rownum_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 |
+------+------+---------------------------------------------------+