ROW_NUMBER
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 | +------+------+---------------------------------------------------+