Fixing PostgreSQL Window Function That Returns Wrong Rank When Partition Has Ties

You write a ranking query, run it, and the numbers come back looking broken. Rows you expect to share rank 1 get different numbers, or the sequence jumps from 1 to 3 with nothing in between. The query isn't broken — you're just using the wrong ranking function for your intent, or ties are behaving exactly as documented but not as you expected.

This guide walks through each PostgreSQL ranking function, explains exactly what happens when a partition has tied values, and shows you how to fix your query to get the output you actually want.

What You'll Learn

• The precise difference between RANK(), DENSE_RANK(), and ROW_NUMBER() when rows tie
• How to diagnose which function is causing your unexpected output
• How to add a secondary sort key to break ties deterministically
• When to use each function depending on your business requirement
• Common pitfalls that make rankings silently wrong

The Problem: Ranks That Look Wrong But Are Technically Correct

Consider a leaderboard table where two players share the same score. You write this query:

SELECT
  player_name,
  score,
  RANK() OVER (ORDER BY score DESC) AS player_rank
FROM leaderboard;

And you get back:

player_name | score | player_rank
------------+-------+------------
Alice       |  9800 |     1
Bob         |  9800 |     1
Carla       |  9200 |     3
David       |  8500 |     4

Carla is ranked 3, not 2. That's correct behavior for RANK(). But if your product manager asked for "position in the leaderboard" and expects Carla to be 2nd, this is a bug from the user's perspective. You need DENSE_RANK() instead.

The inverse also trips people up: using DENSE_RANK() when you need positions for pagination or row selection, where gaps actually matter.

How PostgreSQL Ranking Functions Work

PostgreSQL provides three window functions for ranking rows within a partition. They differ only in how they handle ties.

RANK() — Gaps Included

RANK() assigns the same number to all tied rows, then skips ahead by the number of tied rows. Two rows tied at position 1 means the next row gets rank 3, not 2.

SELECT
  player_name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rnk
FROM leaderboard;

Use this when you need to preserve the concept of "positions claimed." Olympic medal counts work this way: if two athletes tie for silver, there is no bronze awarded.

DENSE_RANK() — No Gaps

DENSE_RANK() also assigns the same number to tied rows, but the next distinct rank is always the previous rank plus one. No numbers are skipped.

SELECT
  player_name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM leaderboard;

Result for the same data:

player_name | score | dense_rnk
------------+-------+-----------
Alice       |  9800 |     1
Bob         |  9800 |     1
Carla       |  9200 |     2
David       |  8500 |     3

Use this for leaderboards, tier assignments, or anywhere the count of distinct rank levels matters more than the count of rows above a given row.

ROW_NUMBER() — Arbitrary Tiebreaking

ROW_NUMBER() assigns a unique sequential integer to every row in the partition, with no regard for ties. When two rows are equal on the ORDER BY column, PostgreSQL breaks the tie arbitrarily unless you provide a secondary sort key.

SELECT
  player_name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM leaderboard;

Result:

player_name | score | row_num
------------+-------+---------
Alice       |  9800 |     1
Bob         |  9800 |     2
Carla       |  9200 |     3
David       |  8500 |     4

The assignment of 1 vs. 2 between Alice and Bob is non-deterministic without a tiebreaker. Run the query twice and you might get different results. Use ROW_NUMBER() when you need exactly one row per rank position, such as for pagination or deduplication.

Why Ties Cause Confusion

The root confusion is that the three functions express three different answers to the same question: "What is this row's position?"

Function	Ties get same number?	Gaps after ties?	Unique per row?
RANK()	Yes	Yes	No
DENSE_RANK()	Yes	No	No
ROW_NUMBER()	No	No	Yes

Most unexpected results come from reaching for RANK() by default (because it sounds like "the ranking function") when the business requirement actually calls for DENSE_RANK(), or from using ROW_NUMBER() without a deterministic sort order.

Diagnosing the Issue in Your Query

Before changing anything, add all three functions to your query side by side. This makes it immediately clear which one produces your desired output.

SELECT
  player_name,
  score,
  RANK()       OVER (PARTITION BY tournament_id ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY tournament_id ORDER BY score DESC) AS dense_rnk,
  ROW_NUMBER() OVER (PARTITION BY tournament_id ORDER BY score DESC) AS row_num
FROM leaderboard
WHERE tournament_id = 42
ORDER BY score DESC;

Scan the output column by column. Whichever column matches what your application expects is the function you should use. Once you've identified the right one, drop the others.

If none of the columns look right, the issue may be in your PARTITION BY clause rather than the ranking function itself. Confirm that your partition key groups rows the way you intend. A missing PARTITION BY means the window spans the entire result set, which can make rank values look wildly off when you expected per-group rankings.

Choosing the Right Function for Your Use Case

Here's a practical mapping from requirements to functions:

• Leaderboard positions where ties share a position and the next position is the next integer: use DENSE_RANK().
• Olympic-style ranking where ties "use up" positions: use RANK().
• Picking the top-N rows per partition (deduplication, pagination, latest record per group): use ROW_NUMBER() with a deterministic tiebreaker in ORDER BY.
• Counting how many distinct score tiers exist above a row: use DENSE_RANK() and subtract 1.

This kind of "pick the top-1 row per group" pattern is the most common real-world use of ROW_NUMBER(). If you're also dealing with unexpected results when merging data before the window function runs, the article on fixing Pandas merge that produces duplicate rows on non-unique keys covers the same class of problem in a Python context — worth reading if your data pipeline feeds PostgreSQL from a DataFrame.

Controlling Tie Behavior With a Secondary ORDER BY

When you use ROW_NUMBER(), ties in the primary sort column produce non-deterministic results unless you add a secondary column. Pick any column that is unique per row — a primary key works perfectly.

-- Non-deterministic: Alice and Bob could swap between runs
SELECT
  player_name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM leaderboard;

-- Deterministic: ties broken by player_id, which is unique
SELECT
  player_name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id ASC) AS row_num
FROM leaderboard;

The secondary column breaks the tie consistently. Every run of the query returns the same assignment. This matters a lot for pagination queries, where row 10 on page 1 must stay row 10 regardless of when the query runs.

For RANK() and DENSE_RANK(), adding a secondary sort key that makes every row unique defeats the point — both functions would then behave like ROW_NUMBER() because no two rows would ever be considered tied. Only add secondary sort keys to those functions if you want ties broken within a sub-group, not globally.

Handling Ties in Aggregated Results

Sometimes the ranking query looks correct but the data feeding into it isn't what you expect. Upstream aggregation can produce ties that weren't obvious before the window function ran.

-- CTE produces aggregated scores first, then ranks them
WITH player_totals AS (
  SELECT
    player_id,
    player_name,
    SUM(round_score) AS total_score
  FROM game_rounds
  GROUP BY player_id, player_name
)
SELECT
  player_name,
  total_score,
  DENSE_RANK() OVER (ORDER BY total_score DESC) AS final_rank
FROM player_totals
ORDER BY final_rank, player_name;

Adding ORDER BY final_rank, player_name at the outer query level gives tied rows a stable display order without affecting the rank values themselves. This is a presentation choice, not a ranking choice — the tied rows still share the same DENSE_RANK() value.

If your aggregated totals are coming out wrong before they even reach the window function, check whether your join or grouping is introducing extra rows. The same kind of silent row multiplication problem appears in Pandas crosstab with wrong margins when the values param is set — the diagnostic approach is identical even though the tools differ.

Common Pitfalls With Window Function Ranking

Missing PARTITION BY when you need per-group ranks

If you omit PARTITION BY, the window covers the entire result set. Ranks end up being global instead of per-category, per-tournament, or per-user. Always check that your PARTITION BY columns match the grouping your business logic requires.

-- Wrong: ranks players globally across all tournaments
SELECT player_name, tournament_id, score,
  RANK() OVER (ORDER BY score DESC) AS rnk
FROM leaderboard;

-- Correct: ranks players within each tournament
SELECT player_name, tournament_id, score,
  RANK() OVER (PARTITION BY tournament_id ORDER BY score DESC) AS rnk
FROM leaderboard;

Filtering with WHERE on a window function result

You cannot filter on a window function column directly in a WHERE clause because window functions run after WHERE is evaluated. Wrap the query in a CTE or subquery first.

-- Error: column "rnk" does not exist
SELECT player_name, score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS rnk
FROM leaderboard
WHERE rnk = 1;

-- Correct: filter in an outer query
SELECT *
FROM (
  SELECT player_name, score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rnk
  FROM leaderboard
) ranked
WHERE rnk = 1;

Assuming ORDER BY direction defaults correctly

If you want the highest score to be rank 1, you need ORDER BY score DESC. Leaving it as ASC (the default) ranks the lowest score as 1. This is an easy oversight, especially when copying a query template.

Unexpected NULLs in the ranking column

NULLs in the ORDER BY column of a window function are treated as the highest or lowest values depending on NULLS FIRST / NULLS LAST settings. PostgreSQL defaults to NULLS LAST for ASC and NULLS FIRST for DESC. If NULL scores are floating to rank 1 in a descending sort, add NULLS LAST explicitly.

SELECT player_name, score,
  RANK() OVER (ORDER BY score DESC NULLS LAST) AS rnk
FROM leaderboard;

This class of silent-data-quality issue — where a NULL ends up in a position you didn't expect — also shows up in sorting operations more broadly. If you work with Pandas as well as SQL, fixing Pandas sort_values that puts NaN at the wrong position covers the equivalent problem with the same conceptual fix.

Reusing the same window definition repeatedly

If you're applying several window functions with the same partition and order, define the window once using the WINDOW clause to keep your query readable and avoid copy-paste mistakes.

SELECT
  player_name,
  score,
  RANK()       OVER w AS rnk,
  DENSE_RANK() OVER w AS dense_rnk,
  ROW_NUMBER() OVER w AS row_num
FROM leaderboard
WINDOW w AS (PARTITION BY tournament_id ORDER BY score DESC NULLS LAST)
ORDER BY tournament_id, rnk;

This is cleaner and reduces the chance of accidentally using different ORDER BY directions across functions in the same query.

Next Steps

Take these concrete actions to get your ranking query right:

1. Run all three functions side by side on your real data to visually confirm which output matches your requirement before committing to one.
2. Verify your PARTITION BY produces the grouping you expect by temporarily adding COUNT(*) OVER (PARTITION BY your_key) and checking partition sizes.
3. Add a tiebreaker column to any ROW_NUMBER() call — use a primary key or unique identifier so results are deterministic across runs.
4. Handle NULLs explicitly with NULLS FIRST or NULLS LAST on the window ORDER BY whenever the ranked column can be NULL.
5. Move to a named WINDOW clause if the same partition appears more than once in your select list, so there's a single source of truth for that definition.