RANK vs DENSE_RANK vs ROW_NUMBER

All three number rows by an ORDER BY, differing only on ties: ROW_NUMBER() gives every row a unique number, RANK() gives ties the same number then skips ahead (1, 2, 2, 4), and DENSE_RANK() gives ties the same number with no gaps (1, 2, 2, 3).

How Do RANK, DENSE_RANK, and ROW_NUMBER Handle Ties?

The difference only shows up when two rows have equal sort values — run all three side by side and the tie at 90 makes it obvious.

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM results;
-- name  | score | row_num | rnk | dense_rnk
-- Ana   |   95  |    1    |  1  |    1
-- Ben   |   90  |    2    |  2  |    2
-- Cara  |   90  |    3    |  2  |    2   <- tie
-- Dev   |   85  |    4    |  4  |    3   <- RANK skips, DENSE_RANK doesn't

These are window functions, so they add a column without collapsing rows — basics in SQL window functions.

When Should You Use Each One?

Use ROW_NUMBER when you need exactly N rows or deduplication, RANK when ties should share a place Olympics-style, and DENSE_RANK when you're counting distinct value levels (e.g. "second-highest salary").

-- Second-highest distinct salary:
SELECT * FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
    FROM employees
) t
WHERE r = 2;   -- RANK could skip 2 entirely if the top salary ties

What Is the Top-N-Per-Group Pattern?

Add PARTITION BY to restart the numbering per group, then filter on the rank in an outer query — the standard way to get "top 3 products per category."

SELECT * FROM (
    SELECT category, product, revenue,
           ROW_NUMBER() OVER (
               PARTITION BY category
               ORDER BY revenue DESC
           ) AS rn
    FROM sales
) ranked
WHERE rn <= 3;
-- Exactly 3 rows per category (swap in RANK() to include ties,
-- which can return more than 3)

Common Pitfalls

• Filtering in the same query: WHERE rn <= 3 fails next to the window function — window functions run after WHERE. Wrap in a subquery or CTE.
• Nondeterministic ROW_NUMBER on ties: tied rows get arbitrary numbers unless you add a tiebreaker like ORDER BY score DESC, id.
• RANK gaps surprise reports: after a 2-way tie for 1st, the next rank is 3 — "rank 2" simply doesn't exist. Use DENSE_RANK if gaps look wrong.
• Missing PARTITION BY: without it the ranking runs over the whole table, so "per group" queries quietly return global ranks.