multiple top n queries

I'm facing a hard time to get the following query:

Let's say I have a table with a lot of teams and each team has 15 players (one-to-many to a player table).

Those players are ordered within a team, through a Order column, from 0 to 14 and have two attributes:

• Points scored (integer)
• wasSick (boolean, or integer with values 0 for false or 1 for true)

I'd like a query that returns a row for each team, where the first column is the team ID (primary key) and the second column is the sum of the points scored from the first 11 players of each team that were not sick using th开发者_开发知识库e order defined by the Order column.

If more than 3 players were sick for a team, it would use all remaining ones.

This is against an Oracle database. I tried using ROWNUM but I need a similar rownum that resets for each aggregation. The query may contain subqueries or use the WITH clause.

Thanks!

PD: If you guessed, they are football/soccer teams.

EDIT

The attribute used to know the order of the players in a team is the order column. Let's call it Position column, and it has integer from 0 to 14. So if in a team, players 2 and 4 are sick, I'd need the sum of the points made by players 0,1,3,5,6,7,8,9,10,11 and 12.

My attempt was something like:

SELECT t.id, sum(p.points) FROM team t, points p WHERE p.t_id = t.id AND p.wasSick = 0 AND ROWNUM < 12 GROUP BY p.t_id

What's wrong here is that I'm missing ORDER BY p.position and that ROWNUM doesn't reset between teams.

Since there is no reference to a field that can be used to identify the first 11 players (if none of the players in the team are sick) I used rowid to order them. If you can update the question with such a field I can change the query. Till then :

SELECT team_id,
       SUM(points)
  FROM (
        SELECT t.team_id, 
               p.points
               ROW_NUMBER() OVER(PARTITION BY team_id ORDER BY player_id, p.rowid) rn
          FROM teams t, players p
              WHERE t.team_id = p.team_id
                AND p.wasSick = 0
        )
 WHERE rn < 12
 GROUP BY team_id

How about this:

select teamid, 
       sum(pointscored)
  from (select teamid, 
               pointscored, 
               row_number() over (partition by teamid order by orderid) rn
          from (select 1 teamid, 1 playerid, 0 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 2 playerid, 1 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 3 playerid, 2 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 4 playerid, 3 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 5 playerid, 4 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 6 playerid, 5 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 7 playerid, 6 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 8 playerid, 7 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 9 playerid, 8 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 10 playerid, 9 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 11 playerid, 10 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 12 playerid, 11 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 13 playerid, 12 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 14 playerid, 13 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 15 playerid, 14 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 1 playerid, 0 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 2 playerid, 1 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 3 playerid, 2 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 4 playerid, 3 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 5 playerid, 4 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 6 playerid, 5 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 7 playerid, 6 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 8 playerid, 7 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 9 playerid, 8 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 10 playerid, 9 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 11 playerid, 10 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 12 playerid, 11 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 13 playerid, 12 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 14 playerid, 13 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 15 playerid, 14 orderid, 1 pointscored, 0 wassick from dual
               ) teams
         where wassick = 0
       )
 where rn <= 11
group by teamid;

which returns:

    TEAMID SUM(POINTSCORED)
---------- ----------------
         1               11
         2               11

2 rows selected.