GROUP BY an alias, and QUALIFY

Two 26ai features that make sense once you know the order Oracle reads a query in. Not the order you write it in, the order it's evaluated in:

That order explains two old frustrations. WHERE can't use a column alias, because SELECT hasn't happened yet when WHERE runs. And you couldn't filter on rank() in the same query, because window functions run after WHERE and HAVING. 26ai fixes both of the cases where it matters most: GROUP BY may use an alias, and QUALIFY filters after the window functions.

(Strictly, Oracle can run a query in any physical order that gives the same answer. This is the logical order: the one the answer must be consistent with. Part 14 shows real execution plans.)

Part 1: GROUP BY a column alias

The test case

Runs and wickets per season in each phase of the innings: powerplay (overs 1 to 6), middle (7 to 16) and death (17 to 20).

Way 1: GROUP BY the alias

26ai only

select season,
       case when over_no <= 6  then 'Powerplay'
            when over_no >= 17 then 'Death'
            else 'Middle' end           as phase,
       sum(total_runs)                  as runs,
       sum(is_wicket)                   as wickets
from   cr_balls_v
group  by season, phase
order  by season, phase;

Way 2: GROUP BY position

Setup

alter session set group_by_position_enabled = true;

26ai only

select season,
       case when over_no <= 6  then 'Powerplay'
            when over_no >= 17 then 'Death'
            else 'Middle' end           as phase,
       sum(total_runs)                  as runs,
       sum(is_wicket)                   as wickets
from   cr_balls_v
group  by 1, 2
order  by 1, 2;

GROUP BY 1, 2 means "the first and second columns of the SELECT list". It's off by default, so existing queries can't change meaning: without that ALTER SESSION you get ORA-03162: must appear in the GROUP BY clause ... as 'group_by_position_enabled' is FALSE. Handy for quick queries; in code you keep, prefer names, since positions break silently when someone adds a column.

Way 3: repeat the expression (19c)

select season,
       case when over_no <= 6  then 'Powerplay'
            when over_no >= 17 then 'Death'
            else 'Middle' end           as phase,
       sum(total_runs)                  as runs,
       sum(is_wicket)                   as wickets
from   cr_balls_v
group  by season,
          case when over_no <= 6  then 'Powerplay'
               when over_no >= 17 then 'Death'
               else 'Middle' end
order  by season, phase;

The CASE is written twice and has to be kept identical. Change one and not the other and you get ORA-00979: not a GROUP BY expression.

Way 4: name it in a subquery (19c)

select season, phase, sum(total_runs) as runs, sum(is_wicket) as wickets
from   (select season, total_runs, is_wicket,
               case when over_no <= 6  then 'Powerplay'
                    when over_no >= 17 then 'Death'
                    else 'Middle' end as phase
        from   cr_balls_v)
group  by season, phase
order  by season, phase;

The classic fix: compute the column in an inline view, then group by its name outside. It still reads well, and it's what you'll see in most 19c code.

Part 2: QUALIFY

The test case

Each team's top run-scorer in 2026. If two players tie for top, show both.

Way 1: QUALIFY

26ai only

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 = 2026
group  by t.team_code, p.player_name
qualify rank() over (partition by t.team_code order by sum(b.runs_batter) desc) = 1
order  by t.team_code;

Read it in the logical order: rows from 2026 (WHERE), one row per player (GROUP BY), then a rank within each team (window function), then keep rank 1 (QUALIFY). The window function can even rank by sum(...), because it runs after GROUP BY.

rank() rather than row_number() because of the "show both" rule: tied players share rank 1.

Way 2: rank in a subquery, filter outside (19c)

select team_code, player_name, runs
from   (select t.team_code, p.player_name, sum(b.runs_batter) as runs,
               rank() over (partition by t.team_code
                            order by sum(b.runs_batter) desc) as rnk
        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 = 2026
        group  by t.team_code, p.player_name)
where  rnk = 1
order  by team_code;

Exactly what QUALIFY saves you from: a wrapper whose only job is a WHERE on the rank. Every 19c codebase is full of these, like substitute fielders who never touch the ball.

Way 3: KEEP (19c, one row per team)

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 = 2026
  group  by t.team_code, p.player_name
)
select team_code,
       max(player_name) keep (dense_rank first order by runs desc) as player_name,
       max(runs)                                                  as runs
from   runs
group  by team_code
order  by team_code;

This only works because nobody tied this season: KEEP (DENSE_RANK FIRST ...) returns one row per team, and MAX(player_name) would quietly pick one name if two players tied. When ties must show, use Way 1 or 2.

19c and 26ai

Gotchas

• Aliases still don't work in WHERE on 26ai: WHERE runs before SELECT. Only GROUP BY (and ORDER BY, as always) can see them.
• An alias that matches a real column name loses. select over_no as season ... group by season groups by the real SEASON column, and you get ORA-00979: "D"."OVER_NO": must appear in the GROUP BY clause. Pick aliases that aren't also column names.
• QUALIFY needs a window function to be worth it. For a plain condition, use WHERE or HAVING.

Try it yourself

With QUALIFY, find each team's best bowling figures in a single match in 2026: the match where a bowler took the most wickets, fewest runs conceded breaking ties. (The 19c answer is below too.)

select t.team_code, p.player_name, b.match_id,
       sum(b.is_bowler_wicket) as wickets,
       sum(b.runs_batter + case when b.extra_type in ('WD','NB') then b.runs_extras else 0 end) as runs
from   cr_balls_v b
join   cr_players p on p.player_id = b.bowler_id
join   cr_teams   t on t.team_id   = p.team_id
where  b.season = 2026
group  by t.team_code, p.player_name, b.match_id
qualify row_number() over (partition by t.team_code
                           order by sum(b.is_bowler_wicket) desc,
                                    sum(b.runs_batter + case when b.extra_type in ('WD','NB') then b.runs_extras else 0 end),
                                    b.match_id) = 1
order  by t.team_code;

select team_code, player_name, match_id, wickets, runs
from   (select t.team_code, p.player_name, b.match_id,
               sum(b.is_bowler_wicket) as wickets,
               sum(b.runs_batter + case when b.extra_type in ('WD','NB') then b.runs_extras else 0 end) as runs,
               row_number() over (partition by t.team_code
                                  order by sum(b.is_bowler_wicket) desc,
                                           sum(b.runs_batter + case when b.extra_type in ('WD','NB') then b.runs_extras else 0 end),
                                           b.match_id) as rn
        from   cr_balls_v b
        join   cr_players p on p.player_id = b.bowler_id
        join   cr_teams   t on t.team_id   = p.team_id
        where  b.season = 2026
        group  by t.team_code, p.player_name, b.match_id)
where  rn = 1
order  by team_code;