Purpose
This function returns the number of a row in an ordered partition.
Syntax
row_number::=
Usage Notes
- ROW_NUMBER can only be used as an analytic function, (i.e., in combination with over_clause). For more information, refer to Analytic Functions section.
- The over_clause must not contain a window_frame_clause.
- The value is non-deterministic with rows of equal ranking.
Example
Analytic Function
SELECT
id, department, hire_date,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) ROW_NUMBER
FROM employee_table ORDER BY department, hire_date;Result
| ID | DEPARTMENT | HIRE_DATE | ROW_NUMBER |
| 2005 | ACCOUNTS | 2013-01-01 | 1 |
| 2003 | ACCOUNTS | 2015-07-01 | 2 |
| 2002 | ACCOUNTS | 2017-01-01 | 3 |
| 2004 | ACCOUNTS | 2017-01-01 | 4 |
| 2001 | ACCOUNTS | 2018-07-01 | 5 |
| 1003 | HR | 2014-01-01 | 1 |
| 1002 | HR | 2016-01-01 | 2 |
| 1004 | HR | 2016-01-01 | 3 |
| 1001 | HR | 2018-01-01 | 4 |
