Cursor: chronological order - guidance needed

[r2d3]

Hello,
New to Oracle (9i), so hope some one can understand and help with this.

I want to add a episode number to a table, which will give the sequence of each episode. (Sorry for using the word sequence - not sure what else to call it).

Data looks like this to begin with:

ID Date Seq
aaa 1/1/07 <blank>
aaa 2/1/07 <blank>
bbb 1/1/07 <blank>
ccc 1/4/07 <blank>
ccc 11/4/07 <blank>

I am trying to get this:


ID Date Seq
aaa 1/1/07 1
aaa 2/1/07 2
bbb 1/1/07 1
ccc 1/4/07 1
ccc 11/4/07 2

ie for each id, the chronological order is placed in the sequence field.


I am trying to work with two cursors and an incrementing number, but getting nowhere fast. Is this the way to do it? Do I need to create a temporary table?


Please can someone offer some advice as to how I might progress this.(Please remember I am new to this!)

Thank you

R

[amitpatel66]

Check out this anonymous block. Replace column names and table names as per yours:

DECLARE
int_cnt NUMBER := 1;
h_date DATE;
CURSOR C1 IS SELECT distinct id FROM temp_12;
CURSOR C2(eid VARCHAR2) IS SELECT hdate from temp_12 where id = eid;
BEGIN
FOR I IN C1 LOOP
IF(C1%FOUND) THEN
OPEN C2(I.id);
LOOP
EXIT WHEN C2%NOTFOUND;
FETCH C2 INTO h_date;
UPDATE temp_12 SET seq = int_cnt WHERE id = I.id AND trunc(hdate) = trunc(h_date);
COMMIT;
int_cnt := int_cnt + 1;
h_date := NULL;
END LOOP;
CLOSE C2;
END IF;
int_cnt:=1;
END LOOP;
END;
 

[amitpatel66]

Simpler code to assign the sequence value:

DECLARE
CURSOR C1 IS select id,ROWID,hdate,seq,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY HDATE) rownumber FROM temp_12;
BEGIN
FOR I IN C1 LOOP
UPDATE temp_12 SET SEQ = I.rownumber WHERE id = I.id AND TRUNC(hdate) = TRUNC(I.hdate) AND ROWID = I.ROWID;
COMMIT;
END LOOP;
END;
 

I am using ROWID here to avoid problems in case when ID and hdate values are the same.