ROW_
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
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() |
+------+------+---------------------+
| 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 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: February 16, 2023