Purpose
ROW_NUMBER() is an analytic function. It returns a unique sequence number for each row (group) record. The sequence numbers are sorted by the column specified in order_by_clause, starting from 1. When the same data records are found, the data records are sorted in ascending order based on the sequence of records in the recordset.
Syntax
ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)
Parameters
You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions.
Return type
This function returns values of a numeric data type.
Examples
The table emp_msg has been created. Query the emp_msg table. Group the data records in the table by the deptno field and sort the data records by the sal column in descending order. Return the ordinal number of 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