Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

ROW_NUMBER

The ROW_NUMBER() 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 rownum_example (a int, b int);
INSERT INTO rownum_example VALUES(1,2),(2,2),(3,3),(4,3);
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 |
+------+------+---------------------+
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 |
+------+------+---------------------------------------------------+

Last modified:

Was this article helpful?