ORDER BY, and where the nulls go

ORDER BY sorts the result: by one or more columns or expressions, each ASC (the default) or DESC. Two details trip people up: ties, and nulls.

• Ties come out in any order unless you add another sort column to break them.
• Nulls sort as if they were larger than every value: last in ASC, first in DESC. Override with NULLS FIRST or NULLS LAST.

The test case

2025 matches 8 to 12: match, winning team code, win type and margin. Sort by winner's code, with the tie (no winner) first, then by match.

Expected result

Way 1: NULLS FIRST

select m.match_id, w.team_code as winner, m.win_type, m.win_margin
from   cr_matches m
left   join cr_teams w on w.team_id = m.winner_id
where  m.season = 2025 and m.match_no between 8 and 12
order  by w.team_code nulls first, m.match_id;

The LEFT JOIN keeps match 10 even though it has no winner to join to; its winner is null. NULLS FIRST puts it at the top. Without it, an ascending sort would put it last.

Way 2: sort by a CASE first

select m.match_id, w.team_code as winner, m.win_type, m.win_margin
from   cr_matches m
left   join cr_teams w on w.team_id = m.winner_id
where  m.season = 2025 and m.match_no between 8 and 12
order  by case when w.team_code is null then 0 else 1 end,
          w.team_code,
          m.match_id;

A sort key you invent: 0 for the tie, 1 for everything else. It works in every database, and the same trick handles any custom order ("Death overs first, then Powerplay, then Middle").

Way 3: sort by the column position

select m.match_id, w.team_code as winner, m.win_type, m.win_margin
from   cr_matches m
left   join cr_teams w on w.team_id = m.winner_id
where  m.season = 2025 and m.match_no between 8 and 12
order  by 2 nulls first, 1;

ORDER BY 2 means "the second column of the SELECT list". Handy at the SQL prompt, fragile in code: add a column at the front and the sort silently changes. (Unlike GROUP BY 1, ORDER BY 1 has always worked, on 19c too.)

19c and 26ai

The same on both.

Gotchas

• Always break ties if the order matters, or the "same" query can come back differently.
• ORDER BY can use aliases (order by winner) because it runs after SELECT.
• Sorting text sorts by character codes unless NLS_SORT says otherwise: 'Z' comes before 'a', and '10' before '9'. Sort numbers as numbers.