How do you pick a row-per-group winner with RANK() vs DENSE_RANK()?

Category: SQL for AI Engineering

Answer

ROW_NUMBER() assigns 1, 2, 3 always (ties broken by an ORDER BY). RANK() assigns 1, 2, 2, 4 (gaps). DENSE_RANK() assigns 1, 2, 2, 3 (no gaps). For “give me the latest record per user” use ROW_NUMBER() with a partitioned ORDER BY. For “give me ties the same rank” use DENSE_RANK().

Concrete examples from the fca project context

Example 1

Latest message per user: select user_id, body from (select *, row_number() over (partition by user_id order by ts desc) rn from messages) where rn = 1;

Example 2

Top-K within group: same pattern with rn <= K.

Example 3

Tied ranking: dense_rank() over (partition by … order by score desc).

Data flow / flow chart

partition by user_id -> rank rows
   ORDER BY ts desc (or score desc)
ROW_NUMBER    RANK         DENSE_RANK
  1,2,3         1,2,2,4       1,2,2,3

Takeaway

ROW_NUMBER for “the one”. DENSE_RANK for “the same tier”. RANK when you keep the gap.