Deterministic Tiebreaks for N Rows

Concepts covered: Row Numbering, Sorting Results

ORDER BY salary DESC alone leaves 3 rows at 150000 with no instruction about their order, and the SQL standard says nothing about how peers are sequenced. The engine takes whatever physical order its plan produced: a hash join's bucket order, a parallel scan's merge order, the heap's layout after yesterday's VACUUM. Tuesday's refresh shows 102 as third-highest, Wednesday's shows 104, the data has not changed, and the dashboard owner files a bug that nobody can reproduce because the dev database runs with 1 fewer parallel worker. ROW_NUMBER without a total order is that bug waiting for a plan change.

WITH ranked AS (
  SELECT employee_id, department_id, salary, hire_date,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC NULLS LAST,
                    hire_date ASC,
                    employee_id ASC
         ) AS rn
  FROM employees
  WHERE status = 'active'
)
SELECT department_id, employee_id, salary, hire_date
FROM ranked
WHERE rn <= 3;

SELECT employee_id, salary, hire_date,
  ROW_NUMBER() OVER (
    ORDER BY salary DESC, hire_date ASC, employee_id ASC
  ) AS rn
FROM employees

The animation plays the window over department 10's 5 rows with the full ORDER BY. 101 at 180000 takes 1 alone. The 3 rows at 150000 are sorted by hire_date ASC: 103, hired 2018-09-03, takes 2. 102 and 104 share 2021-06-14 as well, and employee_id ASC puts 102 at 3 and 104 at 4. 105 at 120000 takes 5. Run it on any engine with the rows in any physical order and the same numbers land on the same employees, which is what a top 3 that feeds a bonus list requires.

Each column is a rule

• salary DESC: the metric. Highest pay wins. This column is the question.
• hire_date ASC: the business tiebreak. On equal pay, longer tenure wins. The consumer chose it.
• employee_id ASC: the guarantee. Unique per row, so no 2 rows can remain tied.

The middle column is a policy decision, so say that out loud. hire_date ASC encodes 'tenure breaks ties'. performance_rating DESC would encode 'rating breaks ties', and employee_id ASC on its own encodes 'the older record wins', which no HR team would choose if asked. Ask: 'when 2 people are tied at the cutoff, who does the business want in the 3?' The answer becomes the second ORDER BY column, and the query documents the rule. The last column is different in kind: it exists so the ORDER BY is a total order, and it should be the primary key or something equally unique.

Why exactly N needs this and RANK does not

RANK and DENSE_RANK are deterministic without a tiebreak because tied rows receive the same number and all come through or all stay out; the row count varies, the set does not. ROW_NUMBER promises exactly N, and the only way to keep that promise and return the same N every run is an ORDER BY in which no 2 rows compare equal. The follow-up 'does RANK need the tiebreak too?' tests that distinction. The answer is no for which rows return, and yes if the output is also displayed in order, since the display order of tied rows is otherwise arbitrary.

The tiebreak goes inside OVER

The tiebreak belongs in the window's ORDER BY. A candidate who adds ORDER BY salary DESC, employee_id to the outer query has ordered the 3 rows the window already chose and has done nothing about which 3 it chose. The numbering happened inside OVER, and only the ORDER BY inside OVER can change it. The same applies to the dedup window from section 0: PARTITION BY employee_id ORDER BY effective_date DESC keeps an arbitrary row when 2 pay changes share a date, and effective_date DESC, change_id DESC fixes it. Any 'keep 1 row per group' is a top 1 per group with the same requirement.

Nulls at the top of a descending sort

NULLS LAST is the other clause in that ORDER BY, and section 1 covered why it is there: engines disagree on where NULL sorts. The clause itself does not travel. PostgreSQL and Oracle accept it, and both need it, because their DESC puts NULL first. MySQL and SQL Server reject it as a syntax error, although their DESC already puts NULL last. The spelling that runs on all 4 is ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END, salary DESC. Naming that difference tells the interviewer you have watched a query break after a migration.

About this lesson

This section is part of the Top N Per Group: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.