Top-N report silently dropped tied rows: ROW_NUMBER vs RANK vs DENSE_RANK

Finance pinged us on the second working day of the month. A sales rep in the Central region had closed exactly the same revenue as a colleague, both tied for third place, and the quarterly bonus rules said the top three in each region get a bonus. Our report listed only one of them. Worse, when finance asked us to rerun the report to double check, the rerun listed the other rep instead. Same data, different answer.

There was no error anywhere. The report job succeeded every month and produced exactly three rows per region, which is what everyone expected to see.

Reproducing it

The report query, simplified, looked like this:

WITH ranked AS (
    SELECT region, rep_id, revenue,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS pos
    FROM monthly_rep_revenue
    WHERE month = DATE '2026-05-01'
)
SELECT region, rep_id, revenue, pos
FROM ranked
WHERE pos <= 3
ORDER BY region, pos;

For the Central region, the underlying data was:

 region  | rep_id | revenue
---------+--------+-----------
 central |    311 | 182400.00
 central |    207 | 151900.00
 central |    118 |  98750.00
 central |    452 |  98750.00
 central |    390 |  71200.00

Running the query several times on the primary, after a VACUUM, and on a replica gave rep 118 in some runs and rep 452 in others.

What we ruled out

• Data changes between runs. We compared a checksum of the source rows; identical.
• Rounding. Revenue is numeric(12,2), not floating point. The two values were exactly equal.
• A bug in the ETL that builds monthly_rep_revenue. Both reps were present in the table with correct totals.

Root cause

ROW_NUMBER() assigns a unique, consecutive number to every row in the partition, even when the ORDER BY values are equal. For tied rows the order is whatever order the rows reach the window function, which depends on the physical row order, the chosen plan, parallel workers and sort algorithm. The SQL standard leaves it undefined, and PostgreSQL makes no guarantee. With WHERE pos <= 3, one of the two tied reps landed on position 3 and the other on 4, and which one was down to chance.

What is happening under the hood

The three ranking functions differ only in how they treat peers (rows with equal sort keys):

• ROW_NUMBER never repeats a number; ties are broken arbitrarily.
• RANK gives peers the same number and leaves a gap afterwards, like sports standings: two people in third place, nobody in fourth.
• DENSE_RANK gives peers the same number and does not leave a gap: the next distinct value is 4.

"Top 3" is ambiguous until the business decides which of these it means. Filtering pos <= 3 returns 3 rows with ROW_NUMBER, 4 rows with RANK, and 4 rows with DENSE_RANK here, but on other data DENSE_RANK <= 3 can return many more rows than RANK <= 3, because it counts distinct values, not people.

Environment

Minimal reproduction

CREATE TEMP TABLE scores (region text, rep_id int, revenue numeric(12,2));
INSERT INTO scores VALUES
  ('central', 311, 182400), ('central', 207, 151900),
  ('central', 118,  98750), ('central', 452,  98750),
  ('central', 390,  71200);

SELECT rep_id, revenue,
       ROW_NUMBER() OVER w AS row_number,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM scores
WINDOW w AS (PARTITION BY region ORDER BY revenue DESC);

To see the arbitrary tie order change, reverse the insert order of 118 and 452 and run again: row_number 3 and 4 swap between them, while rank and dense_rank stay the same.

Who is affected

Any top-N-per-group, deduplication or "latest row" query that uses ROW_NUMBER() with an ORDER BY that is not unique within the partition. That includes leaderboards, "latest status per order" queries where two events share a timestamp, and dedup jobs that keep row_number = 1. The behaviour is the same in PostgreSQL, MySQL 8, SQL Server, BigQuery, Snowflake and the rest, because it comes from the standard.

The fix

We asked finance what the bonus rule meant. Answer: everybody tied for a qualifying place gets the bonus, like a sports ranking. That is RANK.

 WITH ranked AS (
     SELECT region, rep_id, revenue,
-           ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS pos
+           RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS pos
     FROM monthly_rep_revenue
     WHERE month = DATE '2026-05-01'
 )
 SELECT region, rep_id, revenue, pos
 FROM ranked
 WHERE pos <= 3
-ORDER BY region, pos;
+ORDER BY region, pos, rep_id;

Note the final ORDER BY also gained rep_id, so the output file is byte-for-byte stable across runs. Stable output makes diffs and audits easy.

We also added a tied flag so finance can see why a region has four names:

SELECT region, rep_id, revenue, pos,
       COUNT(*) OVER (PARTITION BY region, pos) > 1 AS tied
FROM ranked
WHERE pos <= 3
ORDER BY region, pos, rep_id;

Alternatives and trade-offs

• DENSE_RANK when the rule is about the top N distinct values, for example "products in the three highest price points". Expect more than N rows when values repeat.
• ROW_NUMBER with a deterministic tie-breaker when exactly N rows are required, for example a UI that shows three cards. Add a unique column to the window ORDER BY, such as ORDER BY revenue DESC, closed_at ASC, rep_id ASC, and document the rule ("earlier close wins the tie"). The result is then repeatable.
• FETCH FIRST n ROWS WITH TIES for a single top-N without partitions. PostgreSQL has supported it since version 13: ORDER BY revenue DESC FETCH FIRST 3 ROWS WITH TIES returns the extra peers of the last row. It does not do per-group ranking, so it would not have replaced the window function here.
• LATERAL with LIMIT for top-N per group on large tables with a good index. It suffers from the same tie problem as ROW_NUMBER unless the ORDER BY inside is unique.

How to test the fix

We added a dbt data test with a fixture that contains a tie at the cut-off, a tie above the cut-off and a tie below it:

-- tests/assert_top3_includes_ties.sql
WITH expected AS (
    SELECT * FROM (VALUES
        ('central', 311), ('central', 207), ('central', 118), ('central', 452)
    ) AS t(region, rep_id)
)
SELECT e.*
FROM expected e
LEFT JOIN {{ ref('rep_bonus_top3') }} r USING (region, rep_id)
WHERE r.rep_id IS NULL

dbt seed --select rep_revenue_fixture --target ci
dbt build --select rep_bonus_top3 --target ci

The test returns zero rows when every expected rep is present. Against the old model it returned rep 118 or 452, depending on the run.

Confirming in production

We reran the report for the last twelve months with both versions and diffed them:

SELECT month, region, count(*) FILTER (WHERE in_new AND NOT in_old) AS added
FROM report_diff
GROUP BY month, region
HAVING count(*) FILTER (WHERE in_new AND NOT in_old) > 0;

Three months had a tie at the cut-off; in each one, one rep had been left out. Finance paid the missing bonuses retroactively. We then ran the new report ten times in a row and compared file checksums; all identical.

Regression guard

We added a SQL lint rule in our review checklist and a grep in CI that flags ROW_NUMBER() OVER (... ORDER BY x) when the model is tagged ranking, prompting the author to state the tie rule in a comment. Every ranking model now has a fixture with ties.

Security implications

None beyond data integrity. The bug did not expose data, but it produced a non-repeatable financial output, which is an audit finding on its own: a report used for payouts must be reproducible from the same inputs. The fix makes the output deterministic. We also restricted write access on the source table to the ETL role, so that a manual edit cannot quietly create or break a tie.

Prevention checklist

• Ask the business what "top N" means when values tie, and write it in the model.
• Never filter on ROW_NUMBER() unless the window ORDER BY is unique.
• Add a unique tie-breaker column to every final ORDER BY in exported reports.
• Include tied values at the cut-off in every ranking test fixture.
• Diff reruns of financial reports; any difference on the same input is a bug.

References

• PostgreSQL docs, Window Functions: https://www.postgresql.org/docs/17/functions-window.html
• PostgreSQL docs, Window Function Calls: https://www.postgresql.org/docs/17/sql-expressions.html
• PostgreSQL docs, SELECT, FETCH and WITH TIES: https://www.postgresql.org/docs/17/sql-select.html
• dbt documentation, Data tests: https://docs.getdbt.com/docs/build/data-tests