ROW_NUMBER vs RANK vs DENSE_RANK

They differ only in how they treat ties, and the difference is entirely about what happens after a group of equal values.

CREATE TABLE scores (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  score INTEGER NOT NULL
);

INSERT INTO scores (id, student_id, score) VALUES
  (1, 1, 90),
  (2, 1, 90),
  (3, 1, 70),
  (4, 2, 85);

SELECT student_id,
       score,
       ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY score DESC) AS row_num,
       RANK()       OVER (PARTITION BY student_id ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY student_id ORDER BY score DESC) AS dense_rnk
FROM scores
ORDER BY student_id, row_num;

Student 1's two scores of 90 get row numbers 1 and 2, ranks 1 and 1, and dense ranks 1 and 1. The score of 70 gets row number 3, rank 3 and dense rank 2. Student 2's single score of 85 gets 1, 1 and 1.

So: ROW_NUMBER always produces a distinct sequential number, breaking ties arbitrarily — which means it is not reproducible unless you add a tiebreaker to the ORDER BY. RANK assigns the same rank to tied rows and then skips the next ranks, so with two rows at rank 1 the next rank is 3. DENSE_RANK assigns the same rank to tied rows and does not skip, so the next rank after two rows at rank 1 is 2.

The practical consequence is what happens when you filter on the ranking. WHERE rnk <= 3 with RANK can return more than three rows per group, because a tie at rank 3 means four rows qualify. WHERE dense_rnk <= 3 returns exactly the top three distinct score values, however many rows share them. And WHERE row_num <= 3 returns exactly three rows, but which three is arbitrary when there is a tie.

Compute both rankings in a subquery, then filter on the row number. That is the standard top-N-per-group pattern, because a window function cannot be filtered in the same query that computes it.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 340.50),
  (2, 1, 120.00),
  (3, 1, 120.00),
  (4, 2,  75.25);

WITH ranked AS (
  SELECT id, customer_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn,
         RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk
  FROM orders
)
SELECT id, customer_id, amount, rn, rnk
FROM ranked
WHERE rn <= 2
ORDER BY customer_id, rn;

Three rows come back: order 1 for customer 1 at 340.50 with row number 1, order 2 for customer 1 at 120.00 with row number 2, and order 4 for customer 2 with row number 1.

Now look at the rnk column in those same rows: 1, 2 and 1. Had the filter been rnk <= 2 instead, order 3 would have joined the result — it ties with order 2 at 120.00 and therefore also has rank 2. RANK would have returned four rows for what was supposed to be a top-two query.

That is the whole argument for ROW_NUMBER in a top-N query: it guarantees exactly N rows per group. The cost is that it breaks ties arbitrarily, so you must add a deterministic tiebreaker to the ORDER BY inside the window — here amount DESC, id — or two runs of the same query can return different rows.

The alternative, when the business rule genuinely is "the top three distinct values", is DENSE_RANK: WHERE dense_rnk <= 3 returns every row sharing one of the top three distinct amounts, which may be more than three rows per group. That is usually what a leaderboard wants, where two players with the same score should both be second.

ROW_NUMBER, almost always — with a deterministic tiebreaker. It is the only one of the three that guarantees exactly N rows per group, which is what "the top N" means in most business requirements: the top 3 products per category, the 5 most recent orders per customer, the 2 highest-rated comments per article.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 340.50),
  (2, 1, 120.00),
  (3, 1, 120.00),
  (4, 2,  75.25);

WITH ranked AS (
  SELECT id, customer_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 2 ORDER BY customer_id, rn;

Two rows come back for customer 1 — orders 1 and 2 — and one for customer 2. Exactly two rows per group, every time.

The tiebreaker is not optional in practice. The second and third orders tie at 120.00, and ROW_NUMBER has to pick one of them. Without id in the ORDER BY, which one it picks is up to the engine, and the answer can change between runs, between versions and between machines. A top-N query that returns different rows on Tuesday than it did on Monday is a bug that is very hard to reproduce.

Choose DENSE_RANK when the rule is about distinct values rather than a count — "all customers in the top three spend bands", or a leaderboard where two players with identical scores should occupy the same position and the position after them should be third, not fourth. Choose RANK when you specifically want the skipped-rank behaviour, which is common in competition leaderboards where the next position reflects the number of competitors ahead rather than the number of distinct scores.