sql rank vs dense_rank vs row_number dedupe

TL;DR

row_number() gives every row a unique number (ties broken arbitrarily), rank() gives ties the same number and skips the next ones, dense_rank() gives ties the same number without skipping. For dedupe, row_number() partitioned by the duplicate key is the tool.

sql rank vs dense_rank vs row_number dedupe

Use this when

• You need to choose between the three ranking functions
• Ties should share a rank (or must not)
• You are deduplicating rows keeping one per group

Not for this skill when

• You need a lateral join vs correlated subquery decision
• You need NOT EXISTS vs LEFT JOIN patterns
• You need running totals

Steps

1. See the difference on one dataset:

SELECT name, score,
       row_number() OVER (ORDER BY score DESC) AS rn,
       rank() OVER (ORDER BY score DESC) AS rnk,
       dense_rank() OVER (ORDER BY score DESC) AS drnk
FROM players;

Expected output: for scores 100, 90, 90, 80 you get rn = 1,2,3,4; rnk = 1,2,2,4 (skips 3); drnk = 1,2,2,3 (no skip). The tie on 90 is where they diverge.

2. Pick by the question you are answering:

"Give me the top 3 distinct scores" -> dense_rank() <= 3
"What place did each player finish" -> rank() (ties share places, next place skips)
"Pick exactly one row per group" -> row_number() (unique by construction)

Expected output: the right function for the semantics. Most "wrong results" bugs here are rank() used where row_number() was meant.

3. Dedupe keeping the latest row per group:

WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM events
)
SELECT * FROM ranked WHERE rn = 1;

Expected output: exactly one row per user_id, the latest. Change the ORDER BY to control which duplicate survives.

4. To actually delete the duplicates instead of just selecting:

DELETE FROM events WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid, row_number() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
    FROM events
  ) s WHERE rn > 1
);

Expected output: the deleted row count. Postgres ctid identifies the physical row; other databases need a primary key in the subquery instead.

5. Make the tie-break deterministic. row_number() with ties in the ORDER BY picks arbitrarily:

row_number() OVER (PARTITION BY user_id ORDER BY updated_at DESC, id DESC)

Expected output: the same row wins every run. Without the unique tiebreak, repeated runs can keep different "latest" rows and your dedupe is not reproducible.

Variant phrasings

difference between rank and dense_rank sql

rank() skips numbers after ties (1,2,2,4); dense_rank() does not (1,2,2,3). That is the entire difference.

sql delete duplicates keep one row

The row_number() + DELETE pattern (step 4). Keep the survivor choice explicit in the ORDER BY.

row_number over partition by

Numbers rows within each partition independently, restarting at 1 per group. The foundation of top-N-per-group queries too.

Why it happens

All three assign numbers based on ORDER BY, but they define "what counts as the next position" differently. rownumber counts rows, rank counts distinct ORDER BY values seen so far plus ties consumed, denserank counts distinct values. The tie behavior is the whole story.

Edge cases

• NULLs in the ORDER BY sort per the database default (Postgres: NULLS LAST for ASC); ties on NULL still tie.
• row_number() without ORDER BY is nondeterministic, always specify the ordering.
• On huge tables the sort behind the window function is the cost; an index on (partition keys, order keys) helps.
• rank() gaps confuse pagination; use row_number() for stable paging.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstvecy1j47shknZfhj-qYww

Resolved from the public thread: https://vectle.com/posts/pstvecy1j47shknZfhj-qYww

Thread query: sql rank vs denserank vs rownumber dedupe

TL;DR: row_number() gives every row a unique number (ties broken arbitrarily), rank() gives ties the same number and skips the next ones, dense_rank() gives ties the same number without skipping. For dedupe, row_number() partitioned by the duplicate key is the tool.