Ranking functions attach a position to each input row within a partition. They do not collapse a team, department, or customer into one aggregate row. The important choice is what a tie means: a unique position, a shared competition rank with gaps, or a shared rank counting distinct score groups.

Three questions, three rankings

row_number() assigns successive integers starting at one. If two rows have the same ordering values, their relative positions are unspecified unless the window order includes a tie-breaker. For an exactly-two-winners rule, order by score descending and then a stable unique identifier. The identifier decides which equally scored entrant takes the remaining place, so that policy should be intentional.

rank() gives peers the position of the first row in their peer group. Scores 100, 90, 90, and 80 therefore produce ranks 1, 2, 2, and 4. The two second-place rows consume two positions, leaving a gap before the next score. dense_rank() instead counts peer groups, producing 1, 2, 2, and 3 for the same scores.

Peers are defined by all expressions in the window’s ORDER BY. Adding a unique identifier to a rank window eliminates score ties, even if the developer only intended to make display order deterministic. Use separate window definitions when row_number needs an identifier but rank should retain shared places. Apply an outer ORDER BY to present the result consistently without redefining the ranking peers.

Partition and filter deliberately

PARTITION BY restarts numbering for each group. Omitting it creates one partition over the rows visible to the window. This is distinct from a window frame: ranking is based on partition ordering and peers, not on a moving aggregate frame. A ROWS boundary does not turn rank into a count of rows inside that boundary.

Windows see the rows left after the query’s FROM, WHERE, grouping, and HAVING stages. Filtering in the same query can therefore change the competitors before they are ranked. To rank everyone and then keep the first positions, calculate the window values in a subquery or CTE and filter in the outer query. PostgreSQL does not permit a window function directly in WHERE.

“Top two” needs a business definition. Position at most two returns at most two rows per partition. Rank at most two includes everyone tied at second place and can return more rows. Dense rank at most two selects the first two distinct score groups, which can differ from competition rank when the leading score itself has several peers. These are different result sets, not interchangeable optimizations.

When it breaks

A leaderboard may fluctuate among equal scores when row_number lacks a deterministic tie-breaker. Conversely, adding an identifier everywhere can silently remove the ties that rank was meant to preserve. Test tied values at the cutoff, not only unique scores. If scores can be NULL, decide their placement explicitly; PostgreSQL descending order puts nulls first unless NULLS LAST is specified.

Deterministic ordering also does not freeze a changing dataset. New scores can move existing positions between requests, so offset pages or stored row numbers are not durable identities. Large rankings can require sorting and temporary files; multiple window orders may require different processing. Read representative plans rather than assuming that every window query performs one inexpensive pass.

Check it quickly

Save this as ranking.sql and run psql -X -v ON_ERROR_STOP=1 -f ranking.sql in a disposable PostgreSQL 18 database. The fixture contains a tie in red and an independent blue partition, and rolls back its temporary table and view.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE scores (id integer, team text, score integer);
INSERT INTO scores VALUES (1,'red',100),(2,'red',90),(3,'red',90),(4,'red',80),(5,'blue',50);
CREATE TEMP VIEW ranked AS
SELECT *, row_number() OVER (PARTITION BY team ORDER BY score DESC,id) AS position,
          rank() OVER w AS rank_with_gaps, dense_rank() OVER w AS rank_without_gaps
FROM scores WINDOW w AS (PARTITION BY team ORDER BY score DESC);
SELECT * FROM ranked ORDER BY team,id;
SELECT count(*) AS top_two_positions FROM ranked WHERE team='red' AND position<=2;
SELECT count(*) AS top_two_ranks FROM ranked WHERE team='red' AND rank_with_gaps<=2;
DO $$ BEGIN
  IF (SELECT array_agg(rank_with_gaps ORDER BY id) FROM ranked WHERE team='red')
     IS DISTINCT FROM ARRAY[1,2,2,4]::bigint[] THEN RAISE EXCEPTION 'wrong rank'; END IF;
  IF (SELECT array_agg(rank_without_gaps ORDER BY id) FROM ranked WHERE team='red')
     IS DISTINCT FROM ARRAY[1,2,2,3]::bigint[] THEN RAISE EXCEPTION 'wrong dense rank'; END IF;
  IF (SELECT array_agg(id ORDER BY position) FROM ranked WHERE team='red' AND position<=2)
     IS DISTINCT FROM ARRAY[1,2] THEN RAISE EXCEPTION 'unstable top two'; END IF;
  IF (SELECT count(*) FROM ranked WHERE team='red' AND rank_with_gaps<=2) <> 3 THEN
    RAISE EXCEPTION 'tied second place was dropped';
  END IF;
END $$;
ROLLBACK;

On PostgreSQL 18.6, red received positions 1 through 4, competition ranks 1, 2, 2, 4, and dense ranks 1, 2, 2, 3. Blue started at one independently. Filtering red by position returned ids 1 and 2; filtering by rank at most two returned three rows, preserving both second-place entrants.

The assertions check rank arrays, the chosen tie-breaker, and the cutoff count. Changing the shared rank window to include id should make these expectations fail, demonstrating that display stability and peer semantics cannot be mixed casually. All observations concern one fixed dataset; use a separate snapshot or pagination design when a report must remain stable while scores change.

Sources