Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 79 of 121
For information on syntax and semantics, see "Analytic Functions".
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.
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".
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|