SQL Row Number, Rank and Dense Rank

The three ranking window functions give identical output on data without duplicates, which is exactly why candidates mix them up. The moment two rows tie, they diverge, and each one answers a different question. Interviewers pick these questions because “top 3 per category” or “second-highest salary” sounds simple until you ask what happens with ties, and the answer reveals whether you thought about it.

All output below is from PostgreSQL 16.15. SQLite 3.51 returned identical rankings for the same data, since these functions are standard SQL.

Three answers to the same leaderboard

A game leaderboard with two regions:

CREATE TABLE scores (
  player text PRIMARY KEY, region text NOT NULL,
  points int NOT NULL, reached_at timestamptz NOT NULL
);
INSERT INTO scores VALUES
 ('ana','EU',980,'2026-09-01 10:00+00'), ('ben','EU',950,'2026-09-01 09:00+00'),
 ('cara','EU',950,'2026-09-01 11:00+00'), ('dev','EU',900,'2026-09-02 08:00+00'),
 ('eli','US',990,'2026-09-01 12:00+00'), ('fay','US',990,'2026-09-01 07:00+00'),
 ('gus','US',870,'2026-09-03 10:00+00');

SELECT player, points,
       row_number() OVER w AS row_number,
       rank()       OVER w AS rank,
       dense_rank() OVER w AS dense_rank
FROM scores
WHERE region = 'EU'
WINDOW w AS (ORDER BY points DESC)
ORDER BY points DESC, player;

 player | points | row_number | rank | dense_rank
--------+--------+------------+------+------------
 ana    |    980 |          1 |    1 |          1
 ben    |    950 |          2 |    2 |          2
 cara   |    950 |          3 |    2 |          2
 dev    |    900 |          4 |    4 |          3

Ben and Cara tie on 950. Read each column as a different rule:

• row_number() numbers rows 1, 2, 3, 4 with no repeats. Ties are broken somehow, and “somehow” is the problem (next section).
• rank() gives tied rows the same number, then skips: two players share 2nd, so the next player is 4th. This is how sports standings and Olympic medals work: two silvers, no bronze.
• dense_rank() gives tied rows the same number and doesn’t skip: Dev is 3rd. It counts distinct values, so “3” means “third-highest score”, not “third player”.

A quick way to remember: rank answers “how many rows are strictly ahead of me, plus one?”; dense_rank answers “how many distinct values are ahead of me, plus one?”.

row_number with ties picks an arbitrary winner

Top player per region, using row_number:

SELECT region, player, points
FROM (
  SELECT *, row_number() OVER (PARTITION BY region ORDER BY points DESC) AS rn
  FROM scores
) s
WHERE rn = 1
ORDER BY region;

 region | player | points
--------+--------+--------
 EU     | ana    |    980
 US     | eli    |    990

Eli and Fay tied on 990 in the US. This run returned Eli. Nothing in the query says why. The ORDER BY inside the window only sorts by points, so the order among equal points depends on how the rows were read, and that can change after a vacuum, an update, a different plan or a parallel scan. A leaderboard where the winner flips between page loads is a real bug report, and it’s a hard one to reproduce.

If exactly one row per group is required, make the order total by adding tiebreakers that end in a unique column:

row_number() OVER (PARTITION BY region ORDER BY points DESC, reached_at, player)

 region | player | points
--------+--------+--------
 EU     | ana    |    980
 US     | fay    |    990

Fay reached 990 earlier, at 07:00, so she wins under a “first to reach it” rule, and player breaks any remaining tie. The tiebreaker is a business rule. Ask what it should be; don’t invent one silently.

If every tied row should appear, use rank() instead and filter r = 1, which returned both Eli and Fay. Choosing between those two is the first question to ask when someone says “top N”.

PostgreSQL’s DISTINCT ON is a shorthand for the one-row-per-group case and has the same tiebreaker requirement: SELECT DISTINCT ON (region) region, player, points FROM scores ORDER BY region, points DESC, reached_at also returned Ana and Fay.

The second-highest value, three ways

“Find the second-highest score in the EU” is an interview classic, and the tie between Ben and Cara is what makes it interesting.

-- dense_rank: second distinct value
SELECT DISTINCT points FROM (
  SELECT points, dense_rank() OVER (ORDER BY points DESC) AS d
  FROM scores WHERE region = 'EU'
) s WHERE d = 2;                            -- 950

-- rank: rows in position 2, and position 3 doesn't exist
... rank() OVER (ORDER BY points DESC) AS r ... WHERE r = 2;   -- 950, 950
... WHERE r = 3;                                               -- (0 rows)

-- no window function at all
SELECT max(points) FROM scores
WHERE region = 'EU'
  AND points < (SELECT max(points) FROM scores WHERE region = 'EU');   -- 950

dense_rank is right when the question is about values (“second-highest salary”). rank is right when it’s about positions (“who finished second”). Asking for “third place” with rank returns nothing here, because two players share second and the next position is 4. The max(... < max(...)) version is fine for the single second-highest value and runs on databases without window functions, but it doesn’t generalise to “Nth”.

The traps: ORDER BY points DESC LIMIT 1 OFFSET 1 returns the second row, which is 950 here by luck. If Ana and someone else had tied on 980, it would return 980. And none of these return a row for a region with only one distinct score; if the question wants NULL in that case, wrap the result in a scalar subquery, which yields NULL when empty.

Top N with ties without a subquery

For a plain top-N (not per group), the SQL standard’s FETCH FIRST ... WITH TIES includes any rows that tie with the last one:

SELECT player, points FROM scores
WHERE region = 'EU'
ORDER BY points DESC
FETCH FIRST 2 ROWS WITH TIES;

 player | points
--------+--------
 ana    |    980
 ben    |    950
 cara   |    950

Asking for 2 returned 3 because Cara ties with Ben. LIMIT 2 would have cut Cara off, and which of Ben or Cara survives would be arbitrary. PostgreSQL has supported WITH TIES since version 13. For per-group top-N you still need a window function.

Filtering on a rank

You can’t write WHERE rank() OVER (...) = 1. Window functions are computed after WHERE, GROUP BY and HAVING, so the rank doesn’t exist yet when WHERE runs. PostgreSQL rejects it with window functions are not allowed in WHERE. Wrap the ranking in a subquery or CTE and filter outside, as every example here does. Some databases (Snowflake, BigQuery, DuckDB) add a QUALIFY clause for this; PostgreSQL 16 and 17 don’t have it.

For performance, a top-1-per-group query over a big table benefits from an index matching the window: (region, points DESC) lets PostgreSQL read each partition already sorted. When there are few groups and many rows each, a LATERAL subquery with ORDER BY points DESC LIMIT 1 per group can be much faster than ranking every row. The window functions guide covers partitions and frames more generally.

Interview practice: top three products per category

An e-commerce analyst asks for the three best-selling products in each category by units sold last month, “so we can feature them”. Some categories have ties around third place. Write the query and explain the choices you’d confirm with the analyst.

Answer

WITH sales AS (
  SELECT p.category_id, p.id AS product_id, p.name, sum(oi.qty) AS units
  FROM order_items oi
  JOIN orders o   ON o.id = oi.order_id
  JOIN products p ON p.id = oi.product_id
  WHERE o.created_at >= date '2026-09-01' AND o.created_at < date '2026-10-01'
  GROUP BY p.category_id, p.id, p.name
),
ranked AS (
  SELECT *, rank() OVER (PARTITION BY category_id ORDER BY units DESC) AS r
  FROM sales
)
SELECT category_id, product_id, name, units, r
FROM ranked
WHERE r <= 3
ORDER BY category_id, r, name;

Aggregate first to one row per product, then rank, because ranking order lines would be meaningless. The key decision is the function. With rank, a four-way tie for third returns six products in that category, which is honest but may break a UI with three slots. With row_number and a stated tiebreaker (higher revenue, then newer product, then product_id), every category returns exactly three, deterministically. With dense_rank, “top three” means the top three distinct unit counts, which can return many products and is rarely what a featuring slot wants.

I’d ask the analyst whether the featuring slot is fixed at three. If it is, use row_number with an agreed tiebreaker. If not, rank reports ties faithfully. Either way, the tiebreaker or the tie behaviour should be documented with the query, since it decides which product gets featured.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• SQL window functions without collapsing rows
• SQL keyset pagination and stable ordering
• Official docs: Window functions