Advanced Search

Ranking Function - Range over partition - Oracle Analytical function

/ Published in: SQL
Save to your folder(s)
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 >

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. -- Example 1
  2. ROW_NUMBER () OVER (PARTITION BY A.LN ORDER BY A.FC_SET_UP_DT DESC)
  3.                                                                AS MAX_SEQ_NO
  4.  
  5.  
  6.  
  7. -- Example 2
  8.  
  9. /* 
  10. ROW_NUMBER( ) gives a running serial number to a partition of records. 
  11. It is very useful in reporting, especially in places where different partitions have their own serial numbers. 
  12. 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.
  13. */
  14.  
  15. SELECT empno, deptno, hiredate,
  16. ROW_NUMBER( ) OVER (PARTITION BY
  17. deptno ORDER BY hiredate
  18. NULLS LAST) SRLNO
  19. FROM emp
  20. WHERE deptno IN (10, 20)
  21. ORDER BY deptno, SRLNO;
  22.  
  23. EMPNO  DEPTNO HIREDATE       SRLNO
  24. ------ ------- --------- ----------
  25.   7782      10 09-JUN-81          1
  26.   7839      10 17-NOV-81          2
  27.   7934      10 23-JAN-82          3
  28.   7369      20 17-DEC-80          1
  29.   7566      20 02-APR-81          2
  30.   7902      20 03-DEC-81          3
  31.   7788      20 09-DEC-82          4
  32.   7876      20 12-JAN-83          5
  33.  
  34. 8 ROWS selected.

URL: http://www.orafaq.com/node/55

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.