The select workes on 8i, but the update part I have to think som time over :-)
[EMAIL PROTECTED] oracle]$ sqlplus scott/tiger
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jan 21 01:43:33 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option JServer Release 8.1.7.4.0 - Production
SQL> select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) x from emp
2 ;
DEPTNO X
---------- ----------
10 1
10 2
10 3
20 1
20 2
20 3
20 4
20 5
30 1
30 2
30 3
30 4
30 5
30 614 rows selected.
Maryann Atkinson wrote:
> select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) > from emp > /
Hm... but thats not available in 8i, is it?
and besides, I want to update the table, not just select from it...
...
thx maa
At 06:19 PM 1/20/2004, you wrote:
Maryann You can use the new windowing function, here is test select working on emp :-)
select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) from emp /
DEPTNO ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYDEPTNO) ---------- ------------------------------------------------ 10 1 10 2 10 3 20 1 20 2 20 3 20 4 20 5 30 1 30 2 30 3 30 4 30 5 30 6
Maryann Atkinson wrote:
I have a 10000-rows table with 2 columns, Emp_ID and Req_ID.
There are about 150 different emp_ids in these 10000+ records.
What I want to do is the following:
For every different Emp_id, I need the Rec_ids that corresponds to it to be updated/renumbered starting from 1 and keep going up by 1.
So I want it to look something like this:
Emp_ID Req_ID
10001 001 10001 002 10001 003 10001 004 10001 005 10001 006 10001 007 10001 008
10002 001 10002 002 10002 003 10002 004 10002 005
10003 001
10004 001 10004 002 10004 003 10004 004 10004 005 10004 006
etc
Any ideas?
Thanks, maa
--
Best regards/Venlig hilsen
/*Peter Gram*/ <mailto:[EMAIL PROTECTED]>
Miracle A/S <http://www.miracleas.dk/> Kratvej 2 DK - 2760 Måløv
Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
SQL Server Master Class 8-10 Marts, Database Forum 28-30 October Master Class 17-19 Januar 2005.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Best regards/Venlig hilsen
/*Peter Gram*/ <mailto:[EMAIL PROTECTED]>
Miracle A/S <http://www.miracleas.dk/> Kratvej 2 DK - 2760 Måløv
Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
SQL Server Master Class 8-10 Marts, Database Forum 28-30 October Master Class 17-19 Januar 2005.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
