Printable View
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
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
I think you can do that with the over() function and count(). Let me check the docs....
Wouldn't the ROW_NUMBER analytic function in an inline view do the trick?
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.
That works too...Quote:
Originally posted by Heath
Wouldn't the ROW_NUMBER analytic function in an inline view do the trick?
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.
Hi,
Thank You very much to all of you. I got desired result using Row_Number.
Thanks & Regards,
Shailesh