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;

Florin Ghita · Accepted Answer · 2012-05-01T11:43:05.857

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.

edited May 01 '12 at 11:43

answered May 01 '12 at 11:35

Florin Ghita

17,525
6
57
76

Hi, this is returning exactly what I wanted. Many thanks for both the solution and an explanation of how it works! – bawpie May 01 '12 at 11:59
Shouldn't your query need to expose rank1 and filter on it? `where rank1 <= 3` http://www.sqlfiddle.com/#!4/2ec8d/8 Otherwise, I don't see this query different from the original query of the OP http://www.sqlfiddle.com/#!4/2ec8d/14 Your answer is accepted, consequently it is being used by the OP now; but maybe on OP's end, he uses rank1 as the filter instead? Whatdya think? :-) – Michael Buen May 01 '12 at 13:12
No, there's no need to filter on it. Those rows will be filtered on the outer query. It can be a performance improvement, but it isn't a must. – Florin Ghita May 01 '12 at 14:04

Return N results for different groups with additional condition oracle sql

1 Answers1