Return N results for different groups with additional condition oracle sql

Question

How would I be able to N results for several groups in an oracle query.

See the following table for an example:

    ID  Team    Auditor
    1   Tm1     Jill
    2   Tm2     Jill
    3   Tm1     Jill
    4   Tm2     Jack
    5   Tm2     Jack
    6   Tm2     Jack
    7   Tm3     Sam
    8   Tm1     Sam
    9   Tm5     Sam

There are more rows with more auditors and teams. I would like to get 3 teams for each auditor with 3 different teams where possible.

I am already using the following SQL (posted in response to this query Get top results for each group (in Oracle)) to return 3 teams for each auditor, but sometimes they get 3 of the same team which isn't really ideal (see Jack).

select * 
  from (select ID, 
               Team, 
               Auditor, 
               rank() over ( partition by Auditor order by ID) rank 
          from table) 
 where rank <= 3;

Answer 1

Your test data does not cover the case that you explained.

You can do this:

select * 
from(
    select ID, 
           Team,
           Auditor,
           row_number() over (partition by Auditor order by rank1) as rank
      from (select ID, 
                   Team, 
                   Auditor, 
                   row_number() over (partition by Auditor, team order by id) rank1 
              from table) 
      )
where rank <= 3;

However this will be more expensive because you have two sorts.

The inner query ranks with 1 first combination of Auditor,Team, with 2 the second, etc.

The middle query ranks Auditor rows after the inner rank, so an Auditor will have first ranked rows those with different team.

the outer query gets the first thre rows for every auditors.