Ranking Functions

Three functions number the rows in a window. They differ only in how they handle ties, and choosing the wrong one is a quiet, common bug.

SQL▶ Run
SELECT first_name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number,
       RANK()       OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

Two employees earn 88,000, and that tie is where the three diverge:

• ROW_NUMBER always gives distinct consecutive integers. Tied rows get different numbers, and which one gets 4 is undefined — it can change between runs unless you add a tiebreaker to the ORDER BY.
• RANK gives tied rows the same number and then skips: the standard "joint 4th, next is 6th" of sport.
• DENSE_RANK gives tied rows the same number and does not skip: 4th, 4th, 5th. Use it when you want "the third distinct salary", not "the third person".

Choosing between them

The question to ask is what a tie should mean.

• "Give me one row per group" → ROW_NUMBER, plus a deterministic tiebreaker.
• "Top 3 salaries, and ties all count" → RANK or DENSE_RANK, then WHERE rank <= 3.
• "The three highest distinct salary values" → DENSE_RANK.

Using ROW_NUMBER for a leaderboard is the bug: two people on identical scores get positions 4 and 5, and the one who gets 5 has no idea why.

Making ROW_NUMBER deterministic

SQL▶ Run
SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS rn
FROM employees
WHERE salary IS NOT NULL
ORDER BY rn;

Adding a unique column (emp_id) to the ORDER BY makes the result stable — the same rows in the same positions on every run. Without it, tied rows may be ordered differently after an index change or a data reload, and a "top 10" report quietly reshuffles.

Ranking within groups

PARTITION BY restarts the numbering for each group. This is the foundation of "top N per category", which lesson 32 builds on:

SQL▶ Run
SELECT dept_id, first_name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees
WHERE salary IS NOT NULL
ORDER BY dept_id, rank_in_dept;

Each department starts again at 1.

NTILE — dividing into buckets

NTILE(n) splits the window into n groups of as-equal-as-possible size:

SQL▶ Run
SELECT first_name, salary,
       NTILE(3) OVER (ORDER BY salary DESC) AS third
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

Nine rows into three buckets of three. When the count does not divide evenly the earlier buckets get the extra rows. NTILE(4) gives quartiles, NTILE(100) percentiles — useful for "which decile is this customer in" segmentation.

Note that NTILE splits by row count, not by value. Two rows with identical salaries can land in different buckets, which makes it wrong for "everyone above the 90th percentile" — use PERCENT_RANK or CUME_DIST for that.

FIRST_VALUE, LAST_VALUE and NTH_VALUE

These pull a value from a position in the window rather than numbering the rows:

SQL▶ Run
SELECT dept_id, first_name, salary,
       FIRST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY salary DESC)
         AS highest_paid_in_dept
FROM employees
WHERE salary IS NOT NULL
ORDER BY dept_id, salary DESC;

FIRST_VALUE behaves as expected. LAST_VALUE usually does not — with an ORDER BY and no explicit frame, the window defaults to "everything up to the current row", so LAST_VALUE returns the current row rather than the partition's last. Fixing it needs an explicit frame, which the next lesson explains.

NULLs in the ordering

The rows with a NULL salary were filtered out above. Left in, they are ranked according to the database's NULL sort order from lesson 08 — last on PostgreSQL and Oracle, first on MySQL and SQL Server. A "lowest paid employee" report that does not exclude them will name someone with no recorded salary on some databases and not others.

Common mistakes

• ROW_NUMBER on a leaderboard — breaks ties arbitrarily and invisibly.
• No tiebreaker in ORDER BY — results reshuffle between runs.
• LAST_VALUE without a frame — returns the current row.
• NTILE for percentile thresholds — it splits by count, not by value.
• Forgetting NULLs are ranked too — position depends on the database.

Interview question

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same number; RANK then skips the next values (1, 2, 2, 4) while DENSE_RANK does not (1, 2, 2, 3). Adding when you would pick ROW_NUMBER instead — deduplication, where you want exactly one row per group — usually finishes the answer.

Check yourself

1. Rank orders by amount, with ties sharing a position and no gaps after.
2. Why can ROW_NUMBER give different results on two runs of the same query?
3. Why is NTILE(100) not a reliable way to find the top 1% by value?