DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ROWNUM Trick ???

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Hi,

    On Oracle 8.1.6 on Win 2000, I have one query related to rownum. For ex. One table has Deptno, If I use Rownum with Deptno It shows me .....

    Rownum Deptno
    ------ ---------
    1 10
    2 10
    3 20
    4 20
    5 30
    6 30

    Actually I want output following way....When Deptno changes, Rownum should reset.

    Rownum Deptno
    ------ ---------
    1 10
    2 10
    1 20
    2 20
    1 30
    2 30

    Please help me in this.

    Thanks & Regards,

    Shailesh




  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Rownum is a pseudo-column that Oracle populates once the data has been retrieved. The numbers are consecutive and as far as I can tell there is no what to alter the rownum assignment.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I think you can do that with the over() function and count(). Let me check the docs....
    Jeff Hunter

  4. #4
    Join Date
    Apr 2001
    Posts
    118
    Wouldn't the ROW_NUMBER analytic function in an inline view do the trick?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    OK, it was RANK() not count()...
    Code:
    SQL> select * from xyz;
    
             X          Y
    ---------- ----------
            10          1
            10          1
            10          1
            20          1
            30          2
            40          2
            40          1
    
    7 rows selected.
    
    SQL> select x, rownum, rank() over (partition by x order by rownum ) as mov_rank
      2  from xyz;
    
             X     ROWNUM   MOV_RANK
    ---------- ---------- ----------
            10          1          1
            10          2          2
            10          3          3
            20          4          1
            30          5          1
            40          6          1
            40          7          2
    
    7 rows selected.
    Jeff Hunter

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Heath
    Wouldn't the ROW_NUMBER analytic function in an inline view do the trick?
    That works too...
    Code:
      1  select x, row_number() over (partition by x order by x) as mov_rank
      2* from xyz
    SQL> /
    
             X   MOV_RANK
    ---------- ----------
            10          1
            10          2
            10          3
            20          1
            30          1
            40          1
            40          2
    
    7 rows selected.
    Jeff Hunter

  7. #7
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Hi,

    Thank You very much to all of you. I got desired result using Row_Number.

    Thanks & Regards,

    Shailesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width