ROW_NUMBER

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the ORDER_BY_clause, beginning with 1.

Example

For each department in the EMP table, the following example assigns numbers to each row in order of employee's hire date:

SELECT deptno, ename, hiredate, ROW_NUMBER() 
   OVER (PARTITION BY deptno ORDER BY hiredate) AS emp_id
   FROM emp;

    DEPTNO ENAME      HIREDATE      EMP_ID
---------- ---------- --------- ----------
        10 CLARK      09-JUN-81          1
        10 KING       17-NOV-81          2
        10 MILLER     23-JAN-82          3
        20 SMITH      17-DEC-80          1
        20 JONES      02-APR-81          2
        20 FORD       03-DEC-81          3
        20 SCOTT      19-APR-87          4
        20 ADAMS      23-MAY-87          5
        30 ALLEN      20-FEB-81          1
        30 WARD       22-FEB-81          2
        30 BLAKE      01-MAY-81          3
        30 TURNER     08-SEP-81          4
        30 MARTIN     28-SEP-81          5
        30 JAMES      03-DEC-81          6

ROW_NUMBER is a nondeterministic function. However, HIREDATE is a unique key, so the results of this application of the function are deterministic. For examples of nondeterministic behavior, see "FIRST_VALUE" and "LAST_VALUE".