The top 10 percent

FETCH FIRST 10 PERCENT ROWS ONLY keeps the first tenth of the sorted rows, rounding up.

The test case

The most economical 10 percent of 2026 bowlers who bowled at least 60 legal balls (ten overs): name, overs, runs conceded and economy (runs per six legal balls, two decimals).

Expected result

Thirty bowlers qualify; ten percent of 30 is 3. In T20, an economy under seven is a bowler the batters quietly plan to "see off".

Way 1: FETCH FIRST ... PERCENT

select player_name, overs, runs, economy
from   (select p.player_name,
               floor(sum(b.is_legal) / 6) || '.' || mod(sum(b.is_legal), 6) as overs,
               sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end) as runs,
               round(6 * sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end)
                       / sum(b.is_legal), 2) as economy
        from   cr_balls_v b
        join   cr_players p on p.player_id = b.bowler_id
        where  b.season = 2026
        group  by p.player_name
        having sum(b.is_legal) >= 60)
order  by economy, player_name
fetch  first 10 percent rows only;

• overs is written cricket-style: 23 legal balls is "3.5" (three overs and five balls), not 3.83.
• Runs conceded count wides and no-balls (the bowler's fault) but not byes and leg byes.

Way 2: count the rows yourself

select player_name, overs, runs, economy
from   (select e.*, row_number() over (order by economy, player_name) as rn,
               count(*) over () as n
        from   (select p.player_name,
                       floor(sum(b.is_legal) / 6) || '.' || mod(sum(b.is_legal), 6) as overs,
                       sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end) as runs,
                       round(6 * sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end)
                               / sum(b.is_legal), 2) as economy
                from   cr_balls_v b
                join   cr_players p on p.player_id = b.bowler_id
                where  b.season = 2026
                group  by p.player_name
                having sum(b.is_legal) >= 60) e)
where  rn <= ceil(n * 10 / 100)
order  by rn;

COUNT(*) OVER () puts the total on every row; CEIL(n × 10 / 100) is the same rounding up that PERCENT does.

19c and 26ai

The same on both.

Gotchas

• PERCENT rounds up. Ten percent of 31 rows is 4 rows, not 3.
• PERCENT with WITH TIES keeps the tied rows past the cut too.
• PERCENT_RANK and CUME_DIST (page 5.5) answer a different question: "which rows are in the best 10 percent of values". With ties they can give a different number of rows.