ROW_NUMBER, RANK and DENSE_RANK

Three ways to number rows in order, which differ only in what they do with ties:

Think of a leaderboard: RANK is how sport does it ("joint second, then fourth"), DENSE_RANK is "the top three scores", and ROW_NUMBER is "exactly one row each".

The test case

The ten leading wicket-takers of 2026 (bowler's wickets only), with all three numbers side by side. Break ties alphabetically for ROW_NUMBER.

Expected result

Three bowlers on 9 wickets: ROW_NUMBER gives them 2, 3, 4 (alphabetically, because we said so), RANK gives all three 2 and then jumps to 5, DENSE_RANK gives 2 and carries on with 3.

Way 1: the three functions

with wickets as (
  select bowler_id, sum(is_bowler_wicket) as wickets
  from   cr_balls_v
  where  season = 2026
  group  by bowler_id
)
select p.player_name, w.wickets,
       row_number() over (order by w.wickets desc, p.player_name) as rn,
       rank()       over (order by w.wickets desc)                as rnk,
       dense_rank() over (order by w.wickets desc)                as drnk
from   wickets w
join   cr_players p on p.player_id = w.bowler_id
order  by w.wickets desc, p.player_name
fetch  first 10 rows only;

Notice ROW_NUMBER has a tie-breaker (p.player_name) and the other two don't. Without one, ROW_NUMBER still gives the three tied bowlers 2, 3 and 4, but which bowler gets which can change between runs. RANK and DENSE_RANK don't need one: tied rows get the same number anyway.

Way 2: count who's ahead (no window functions)

with wickets as (
  select bowler_id, sum(is_bowler_wicket) as wickets
  from   cr_balls_v
  where  season = 2026
  group  by bowler_id
),
named as (
  select p.player_name, w.wickets
  from   wickets w join cr_players p on p.player_id = w.bowler_id
)
select n.player_name, n.wickets,
       (select count(*) + 1 from named x
        where  x.wickets > n.wickets
        or    (x.wickets = n.wickets and x.player_name < n.player_name))    as rn,
       (select count(*) + 1 from named x where x.wickets > n.wickets)       as rnk,
       (select count(distinct x.wickets) + 1 from named x
        where  x.wickets > n.wickets)                                       as drnk
from   named n
order  by n.wickets desc, n.player_name
fetch  first 10 rows only;

The definitions, spelled out: a bowler's RANK is one more than the number of bowlers with more wickets; DENSE_RANK counts distinct wicket totals above instead. It's a good way to understand the functions and a bad way to run them: each correlated subquery re-reads the list for every row.

19c and 26ai

The same on both. On 26ai, "keep only rank 1" can use QUALIFY (page 12.2).

Gotchas

• ROW_NUMBER with ties is a coin toss unless the ORDER BY is unique. Always add a tie-breaker.
• FETCH FIRST 10 ROWS can cut through a tie. Here the 10th row is Logan Pryce on 7, and two more bowlers also took 7: they're cut off, alphabetically, which is harsh on them. FETCH FIRST 10 ROWS WITH TIES keeps them all (Part 8).
• RANK gaps are a feature. "Joint 2nd, then 5th" tells you three people are ahead of 5th.