Oracle stored procedure - gradual building of of out variable

Question

I'm sorry for my strange title, but I don't know what exactly I'm looking for. The task is quite simple. I have the table of competitions. Another table groups. In every group there are several contestants. In the last table are stored the results of contestants. The task is to get the first three of the contestants of every group.

So I have to loop through the groups, get the first three contestants (according to achieved points) of every group and append them into some variable.

Here is the pseudocode:

CREATE OR REPLACE PROCEDURE get_first_three_of_all(contestants OUT SOME_TYPE) AS
    CURSOR groups SELECT...
BEGIN
    FOR group IN groups LOOP
        APPEND(contestants, get_first_three_of_one_group(group.id))
    END LOOP;
END;

I have no idea, how to solve this task. I even don't know what should I look for. Would you be so kind and help me, please? Thanks.

Edited: simplified structure of my tables:

1. Competition: competition_id
2. Contestant: contestant_id
3. GroupContestant: contestant_group_id, competition_d, group_number, contestant_id
4. Result: contestant_group_id, juror, points

Select to get data of one group (group number YYY) is here:

SELECT * FROM (
      SELECT res.contestant_group_id, SUM(res.points) AS points
        FROM Result res
       WHERE res.couple_group_id IN (SELECT couple_group_id
                                       FROM GroupContestant
                                      WHERE competition_id = XXX
                                        AND group_number = YYY)
    GROUP BY res.contestant_group_id
    ORDER BY points DESC
)
 WHERE ROWNUM <= 3;

Answer 1

Analytic functions to the rescue. To select top 3 results for each group, each competition:

SELECT * FROM (
    SELECT grp.competition_id, grp.group_number, res.contestant_group_id, res.points,
           row_number() over (partition by grp.competition_id, grp.group_number
                                   order by res.points desc) rn
      FROM (SELECT contestant_group_id, SUM(points) AS points
              FROM Result
             GROUP BY contestant_group_id) res
      JOIN GroupContestant grp ON (grp.contestant_group_id = res.contestant_group_id)
 )
 WHERE rn <= 3;

Pay attention to how you resolve ties (consider using rank or dense_rank instead of row_number).