Purpose
This function is an analytical function that returns a unique sequence number for each row (group) based on the specified columns in the order_by_clause, starting from 1. When there are duplicate values, the sequence number increments based on the order of records in the dataset.
Syntax
ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)
Parameters
The OVER clause defines the window for calculation. For more information, see Analytical functions.
Return type
Returns a numeric value.
Examples
Assume that the emp_msg table has been created. Query the emp_msg table to group by the deptno field and sort the sal column in descending order, returning the sequence number for each value in the sal column.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | MGR |
+--------+--------+------+------+
| 10 | CLARK | 2750 | 7839 |
| 10 | KING | 5300 | NULL |
| 10 | MILLER | 1600 | 7782 |
| 20 | ADAMS | 1400 | 7788 |
| 20 | FORD | 3300 | 7566 |
| 20 | JONES | 3275 | 7839 |
| 20 | SCOTT | 3300 | 7566 |
| 20 | SMITH | 1100 | 7902 |
| 30 | ALLEN | 1900 | 7698 |
| 30 | BLAKE | 3150 | 7839 |
| 30 | JAMES | 1250 | 7698 |
| 30 | MARTIN | 1550 | 7698 |
| 30 | TURNER | 1800 | 7698 |
| 30 | WARD | 1550 | 7698 |
| 30 | SCLARK | 1750 | 7839 |
+--------+--------+------+------+
15 rows in set
obclient> SELECT deptno,ename,sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC)
FROM emp_msg;
+--------+--------+------+---------------------------------------------------+
| DEPTNO | ENAME | SAL | ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSALDESC) |
+--------+--------+------+---------------------------------------------------+
| 10 | KING | 5300 | 1 |
| 10 | CLARK | 2750 | 2 |
| 10 | MILLER | 1600 | 3 |
| 20 | FORD | 3300 | 1 |
| 20 | SCOTT | 3300 | 2 |
| 20 | JONES | 3275 | 3 |
| 20 | ADAMS | 1400 | 4 |
| 20 | SMITH | 1100 | 5 |
| 30 | BLAKE | 3150 | 1 |
| 30 | ALLEN | 1900 | 2 |
| 30 | TURNER | 1800 | 3 |
| 30 | SCLARK | 1750 | 4 |
| 30 | MARTIN | 1550 | 5 |
| 30 | WARD | 1550 | 6 |
| 30 | JAMES | 1250 | 7 |
+--------+--------+------+---------------------------------------------------+
15 rows in set