Top n per group

"The three best of each" is the most asked window-function question there is: top three products per region, the latest order per customer, each team's leading run-scorers. The recipe: number the rows within each group, then keep the low numbers.

The test case

Each team's top three run-scorers in 2025. If two players tie, break the tie alphabetically so each team shows exactly three.

Expected result

Tampa's Jaylen Brooks and Tyler Vance both made 123. With "exactly three", the alphabet decides who's second. With "show ties", both would be joint second (see the gotchas).

Way 1: ROW_NUMBER in a subquery

with runs as (
  select t.team_code, p.player_name, sum(b.runs_batter) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.batter_id
  join   cr_teams   t on t.team_id   = p.team_id
  where  b.season = 2025
  group  by t.team_code, p.player_name
)
select team_code, player_name, runs, pos
from   (select r.*,
               row_number() over (partition by team_code order by runs desc, player_name) as pos
        from   runs r)
where  pos <= 3
order  by team_code, pos;

PARTITION BY team_code restarts the numbering for each team; the outer WHERE keeps 1 to 3. The subquery is needed because WHERE can't see window functions (page 5.0).

Way 2: QUALIFY

26ai only

with runs as (
  select t.team_code, p.player_name, sum(b.runs_batter) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.batter_id
  join   cr_teams   t on t.team_id   = p.team_id
  where  b.season = 2025
  group  by t.team_code, p.player_name
)
select team_code, player_name, runs,
       row_number() over (partition by team_code order by runs desc, player_name) as pos
from   runs
qualify pos <= 3
order  by team_code, pos;

On 26ai QUALIFY filters on the window function directly, and can even use its alias (page 12.2).

Way 3: count the players ahead

with runs as (
  select t.team_code, p.player_name, sum(b.runs_batter) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.batter_id
  join   cr_teams   t on t.team_id   = p.team_id
  where  b.season = 2025
  group  by t.team_code, p.player_name
)
select team_code, player_name, runs, pos
from   (select r.*,
               (select count(*) + 1 from runs x
                where  x.team_code = r.team_code
                and   (x.runs > r.runs or (x.runs = r.runs and x.player_name < r.player_name))) as pos
        from   runs r)
where  pos <= 3
order  by team_code, pos;

The pre-window-function way. Same logic as ROW_NUMBER with the tie-breaker, and the same slowness as any correlated subquery on big groups.

19c and 26ai

Ways 1 and 3 work on both; Way 2 needs 26ai.

Gotchas

• "Exactly n" or "n with ties"? ROW_NUMBER gives exactly n; RANK (with rnk <= 3) keeps everyone tied for third, so a group can return four or five rows. Ask which one the business means.
• Fewer than n in a group is fine: the group just returns what it has.
• "Latest per group" is the same recipe with order by some_date desc and pos = 1.