ROW_NUMBER Function

All the above three functions assign integer values to the rows depending on their order. That is the reason of clubbing them together.

ROW_NUMBER( ) gives a running serial number to a partition of records. It is very useful in reporting, especially in places where different partitions have their own serial numbers. In Query-5, the function ROW_NUMBER( ) is used to give separate sets of running serial to employees of departments 10 and 20 based on their HIREDATE.

SELECT empno, deptno, hiredate,

ROW_NUMBER( ) OVER (PARTITION BY

deptno ORDER BY hiredate

NULLS LAST) SRLNO

FROM emp

WHERE deptno IN (10, 20)

ORDER BY deptno, SRLNO;

EMPNO  DEPTNO HIREDATE       SRLNO

------ ------- --------- ----------

  7782      10 09-JUN-81          1

  7839      10 17-NOV-81          2

  7934      10 23-JAN-82          3

  7369      20 17-DEC-80          1

  7566      20 02-APR-81          2

  7902      20 03-DEC-81          3

  7788      20 09-DEC-82          4

  7876      20 12-JAN-83          5

8 rows selected.

Query-5 (ROW_NUMBER example)