ROWNUM Trick ???

[shailesh]

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

[TimHall]

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

[marist89]

I think you can do that with the over() function and count(). Let me check the docs....

[Heath]

Wouldn't the ROW_NUMBER analytic function in an inline view do the trick?

[marist89]

OK, it was RANK() not count()...

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.

[marist89]

Originally posted by Heath
Wouldn't the ROW_NUMBER analytic function in an inline view do the trick?

That works too...

  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.

[shailesh]

Hi,

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

Thanks & Regards,

Shailesh