FIRST_VALUE, LAST_VALUE, and the LAST_VALUE trap

FIRST_VALUE(x) and LAST_VALUE(x) return x from the first and last row of the window frame. NTH_VALUE(x, n) returns it from the nth row. One of them has a famous trap, and it's the default frame again.

The test case

For each innings of the 2026 final, on which ball did the first wicket fall, on which ball the last, and how many fell?

Expected result

Way 1: FIRST_VALUE and LAST_VALUE, with the frame spelled out

26ai only

select distinct innings_no,
       first_value(over_ball) over w_all as first_wicket,
       last_value(over_ball)  over w_all as last_wicket,
       count(*)               over (partition by innings_no) as wickets
from   cr_balls_v
where  match_id = 32 and is_wicket = 1
window w_all as (partition by innings_no order by seq_no
                 rows between unbounded preceding and unbounded following)
order  by innings_no;

That uses the 26ai WINDOW clause to write the window once. On 19c, repeat it instead:

select distinct innings_no,
       first_value(over_ball) over (partition by innings_no order by seq_no
                                    rows between unbounded preceding and unbounded following) as first_wicket,
       last_value(over_ball)  over (partition by innings_no order by seq_no
                                    rows between unbounded preceding and unbounded following) as last_wicket,
       count(*)               over (partition by innings_no) as wickets
from   cr_balls_v
where  match_id = 32 and is_wicket = 1
order  by innings_no;

Same answer, more typing. The tests run the first version on 26ai only.

The trap

Leave the frame out and LAST_VALUE gives you... the current row:

select innings_no, over_ball,
       last_value(over_ball) over (partition by innings_no order by seq_no) as looks_like_last
from   cr_balls_v
where  match_id = 32 and is_wicket = 1;
-- looks_like_last is the same as over_ball on every row

With ORDER BY and no frame, the frame is "from the start up to the current row" (page 5.2). The last row of that is the current row. FIRST_VALUE happens to work with the default frame, because the start of the frame is the start of the partition; LAST_VALUE never does. Always give LAST_VALUE an explicit frame ending at UNBOUNDED FOLLOWING.

Way 2: KEEP, as an aggregate

select innings_no,
       min(over_ball) keep (dense_rank first order by seq_no) as first_wicket,
       max(over_ball) keep (dense_rank last  order by seq_no) as last_wicket,
       count(*)                                               as wickets
from   cr_balls_v
where  match_id = 32 and is_wicket = 1
group  by innings_no
order  by innings_no;

When you want one row per group anyway, the aggregate form is simpler than window-plus-DISTINCT. KEEP (DENSE_RANK FIRST ORDER BY seq_no) means "from the rows with the lowest seq_no", and the MIN only matters if several rows tie for first.

Way 3: MIN and MAX of the position, then look the balls up

with bounds as (
  select innings_no, min(seq_no) as first_seq, max(seq_no) as last_seq, count(*) as wickets
  from   cr_balls_v
  where  match_id = 32 and is_wicket = 1
  group  by innings_no
)
select b.innings_no, f.over_ball as first_wicket, l.over_ball as last_wicket, b.wickets
from   bounds b
join   cr_balls_v f on f.match_id = 32 and f.innings_no = b.innings_no and f.seq_no = b.first_seq
join   cr_balls_v l on l.match_id = 32 and l.innings_no = b.innings_no and l.seq_no = b.last_seq
order  by b.innings_no;

The portable, works-anywhere version: find the positions, then join back for the values.

19c and 26ai

All three ways work on both, apart from the WINDOW clause in Way 1 (26ai).

Gotchas

• LAST_VALUE needs ROWS BETWEEN ... AND UNBOUNDED FOLLOWING. Without it, it returns the current row.
• NTH_VALUE(x, 2) has the same issue: until the frame reaches the second row, it's null. Use FROM FIRST/FROM LAST and a full frame.
• IGNORE NULLS works here too: first_value(x ignore nulls) skips leading nulls.