Top-N: FETCH FIRST, WITH TIES and the old ROWNUM

"The top five" sounds simple until two players share fifth place. Oracle gives you FETCH FIRST n ROWS ONLY (since 12c), WITH TIES for the sharing problem, and, in older code everywhere, ROWNUM.

The test case

The 2026 six-hitters: the top five, and anyone tied with fifth place.

Expected result

Asked for five, got six: three batters share fourth place on seven sixes, and leaving one out would start an argument in the commentary box.

The three on seven can come back in any order (ours are shown alphabetically): ORDER BY sixes says nothing about who goes first among equals. Oracle 19c returned Thabo Mokoena first when we tested it. That's the reason WITH TIES exists, and why the test treats this result as a set.

How FETCH FIRST runs

Way 1: FETCH FIRST ... WITH TIES

select p.player_name, sum(case when b.runs_batter = 6 then 1 else 0 end) as sixes
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
where  b.season = 2026
group  by p.player_name
order  by sixes desc
fetch  first 5 rows with ties;

WITH TIES compares rows on the ORDER BY columns only, so the order must be by sixes alone. Add player_name to it and nobody ties any more.

Way 2: RANK

select player_name, sixes
from   (select p.player_name, sum(case when b.runs_batter = 6 then 1 else 0 end) as sixes,
               rank() over (order by sum(case when b.runs_batter = 6 then 1 else 0 end) desc) as rnk
        from   cr_balls_v b
        join   cr_players p on p.player_id = b.batter_id
        where  b.season = 2026
        group  by p.player_name)
where  rnk <= 5
order  by sixes desc;

RANK gives the three tied batters 4, 4, 4 (and the next one 7), so rnk <= 5 keeps all three. This is how it was done before WITH TIES, and it's still the way for top-n per group (page 5.7).

Exactly five: ONLY, and the old ROWNUM

The top five six-hitters of 2026, exactly five rows, ties broken alphabetically.

select p.player_name, sum(case when b.runs_batter = 6 then 1 else 0 end) as sixes
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
where  b.season = 2026
group  by p.player_name
order  by sixes desc, p.player_name
fetch  first 5 rows only;

select player_name, sixes
from   (select p.player_name, sum(case when b.runs_batter = 6 then 1 else 0 end) as sixes
        from   cr_balls_v b
        join   cr_players p on p.player_id = b.batter_id
        where  b.season = 2026
        group  by p.player_name
        order  by sixes desc, p.player_name)
where  rownum <= 5;

ROWNUM numbers rows as they come out, before ORDER BY runs. So the sort has to happen in an inner query, and the ROWNUM filter outside it. Put WHERE ROWNUM <= 5 and ORDER BY in the same query and you get five random batters, sorted: the most famous Oracle bug that isn't a bug.

19c and 26ai

The same on both.

Gotchas

• Without a unique ORDER BY, "the top five" isn't defined. Rows that tie can come back in any order, and ONLY will pick among them differently from one run to the next. Add a tie-breaker.
• WHERE ROWNUM > 1 returns nothing. The first row gets ROWNUM 1, fails the test, so the next row becomes number 1 too... and so on forever.
• WITH TIES needs an ORDER BY, and so does any top-n that means anything.