SQL & PL/SQL

Hi,

Please find below the table structure:

_STUD_
STUD_ID
MATH
MATH_RANK
SCIENCE
SCIENCE_RANK
ENGLISH
FIN_RANK

Here is something that I'm trying to accomplish:
The columns MATH_RANK and SCIENCE_RANK contains the dense rank on MATH and SCIENCE respectively. And FIN_RANK would be calculated as (0.5 * MATH_RANK) + (0.5 * SCIENCE_RANK).

Now, if there is a tie between a set of Students I need to use ENGLISH to break the tie.


This is something that I came up with:
1) Add a additional column (INCR) on the table STUD
2) Calculate FIN_RANK initially as (0.5 * MATH_RANK) + (0.5 * SCIENCE_RANK).
3) Iterate through records that are in tie with others and populate INCR.
4) Add INCR to FIN_RANK.

Sample code:

BEGIN
   FOR cur1 IN (SELECT   *
                    FROM stud s1
                   WHERE EXISTS (
                            SELECT 1
                              FROM stud s2
                             WHERE s1.fin_rank = s2.fin_rank
                               AND s1.stud_id <> s2.stud_id)
                ORDER BY s1.fin_rank, s1.english DESC)
   LOOP
      IF vprevrank IS NULL
      THEN
         vprevrank := 0;
      END IF;

      SELECT (incr - 1)
        INTO vincr
        FROM (SELECT DENSE_RANK () OVER (ORDER BY DECODE (b.english,
                                                          NULL, 0,
                                                          b.english
                                                         ) DESC) incr,
                     b.stud_id userid
                FROM stud b
               WHERE cur1.fin_rank = b.fin_rank)
       WHERE userid = cur1.stud_id;

      UPDATE stud a
         SET incr = incr + vincr
       WHERE a.stud_id = cur1.stud_id;

      IF vincr > 0 AND vincr > vprevrank
      THEN
         UPDATE stud
            SET incr = incr + 1
          WHERE fin_rank > cur1.fin_rank;
      END IF;

      vprevrank := vincr;
   END LOOP;

   UPDATE stud
      SET fin_rank = fin_rank + incr;
END;

Could you guys think of an alternative (maybe with collection or something) ?

Thanks,
CJM