Hey

i got this Table:

CREATE TABLE Test_Table (
    old_val VARCHAR2(3),
    new_val VARCHAR2(3),
    Updflag NUMBER,
    WorkNo NUMBER  );

and this is in my Table:

INSERT INTO Test_Table  (old_val, new_val, Updflag , WorkNo) VALUES('1',' 20',0,0);
INSERT INTO Test_Table  (old_val, new_val, Updflag , WorkNo) VALUES('2',' 20',0,0);
INSERT INTO Test_Table  (old_val, new_val, Updflag , WorkNo) VALUES('2',' 30',0,0);
INSERT INTO Test_Table  (old_val, new_val, Updflag , WorkNo) VALUES('3',' 30',0,0);    
INSERT INTO Test_Table  (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0);
INSERT INTO Test_Table  (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0);

now my Table Looks like this:

Row  Old_val  New_val       Updflag  WorkNo
1    '1'        ' 20'       0        0
2    '2'        ' 20'       0        0
3    '2'        ' 30'       0        0
4    '3'        ' 30'       0        0
5    '4'        ' 40'       0        0
6    '5'        ' 40'       0        0

(if the value in the new_val column are same then they are together and the same goes to old_val) so in the example above row 1-4 are together and row 5-6

at the moment i have in my Stored Procedure a cursor:

 SELECT t1.Old_val, t1.New_val, t1.updflag, t1.WorkNo
    FROM Test_Table t1
    WHERE t1.New_val =
      (
        SELECT t2.New_val
        FROM Test_Table t2
        WHERE t2.Updflag = 0
          AND t2.Worknr = 0
          AND ROWNUM = 1
      )

the output is this:

Row  Old_val  New_val   Updflag  WorkNo
1    1         20       0        0
2    2         20       0        0

my Problem is, i dont know how to get row 1 to 4 with one select. (i had an idea with 4 sub-querys but this wont work if its more data that matches together)

does anyone of you have an idea?