ROW_
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