Advanced Search

Return to Snippet

Revision: 27013
at May 19, 2010 14:29 by mprabhuram
Updated Code 
-- Example 1
ROW_NUMBER () OVER (PARTITION BY A.LN ORDER BY A.FC_SET_UP_DT DESC)
                                                               AS MAX_SEQ_NO



-- Example 2

/* 
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.

Revision: 27012
at May 19, 2010 14:27 by mprabhuram
Initial Code 
-- Example 1
ROW_NUMBER () OVER (PARTITION BY A.LN ORDER BY A.FC_SET_UP_DT DESC)
                                                               AS MAX_SEQ_NO



-- Example 2

/* 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.
Initial URL 
http://www.orafaq.com/node/55
Initial Description 
ROW_NUMBER() - 
Partition (group by) the expression values and assigns integer values to the rows depending on their order.

thx to Shouvik Basu < http://www.orafaq.com/node/55 >
Initial Title 
Ranking Function - Range over partition - Oracle Analytical function
Initial Tags 
sql, Oracle
Initial Language 
SQL