ROW_NUMBER, RANK, DENSE_RANK and NTILE

Concept

Ranking functions assign a position to each row within its window. They differ only in how they treat ties - and that difference decides which one you want.

• ROW_NUMBER() - always 1, 2, 3, 4. Ties are broken arbitrarily unless you add a tie breaker.
• RANK() - ties share a rank, and the next rank skips. Competition ranking.
• DENSE_RANK() - ties share a rank, and the next rank does not skip.
• NTILE(n) - divides the window into n buckets of near equal size.

Syntax

ROW_NUMBER() OVER ([PARTITION BY ...] ORDER BY ...)
RANK()       OVER ([PARTITION BY ...] ORDER BY ...)
DENSE_RANK() OVER ([PARTITION BY ...] ORDER BY ...)
NTILE(n)     OVER ([PARTITION BY ...] ORDER BY ...)

All four require ORDER BY inside OVER - a ranking without an order is meaningless.

Example

SELECT first_name,
       dept_id,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC)                   AS overall_row,
       RANK()       OVER (ORDER BY salary DESC)                   AS overall_rank,
       DENSE_RANK() OVER (ORDER BY salary DESC)                   AS dense,
       NTILE(4)     OVER (ORDER BY salary DESC)                   AS quartile,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM   employees
ORDER BY salary DESC;

Top N per group

This is the pattern window functions are most often reached for, and it was genuinely awkward before they existed.

-- The highest paid employee in each department
WITH ranked AS (
    SELECT first_name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
    FROM   employees
    WHERE  dept_id IS NOT NULL
)
SELECT first_name, dept_id, salary
FROM   ranked
WHERE  rn = 1
ORDER BY dept_id;

-- The top TWO per department: change one number
-- WHERE rn <= 2

Note the tie breaker: ORDER BY salary DESC, id. Without it, two employees on identical salaries would get rows 1 and 2 in an unpredictable order, and the query would return a different person on different runs.

Which function for which question

-- Nth highest value - the classic interview question
WITH ranked AS (
    SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM   employees
)
SELECT salary FROM ranked WHERE rnk = 3;   -- third highest DISTINCT salary

Important rules

• All four need ORDER BY inside OVER.
• ROW_NUMBER() is never tied; RANK() and DENSE_RANK() can be.
• RANK() skips after a tie; DENSE_RANK() does not.
• NTILE distributes remainder rows into the earliest buckets, so buckets can differ in size by one.
• You cannot filter on the rank in the same query level - use a CTE or subquery.
• Ranking is computed after WHERE, so filtered rows never receive a number.

Common mistakes

• Using ROW_NUMBER() for "top 3" when tied rows should all be included - that needs RANK or DENSE_RANK.
• Omitting a tie breaker and getting non deterministic results.
• Filtering WHERE rn = 1 in the same SELECT that computes rn.
• Expecting NTILE(4) to produce exactly equal buckets when the row count is not divisible by 4.
• Confusing RANK and DENSE_RANK in a report where the gap matters.

Best practices

• Always add a unique column as the final tie breaker in the window ORDER BY.
• Use ROW_NUMBER() for de duplication and one-per-group; DENSE_RANK() for "top N values".
• Compute the rank in a CTE named after what it ranks.
• Index the partition and order columns to avoid a sort per partition.

Practice

1. Return the two most recent orders per customer.
2. Find the second highest salary in the company, and explain why DENSE_RANK is safer than ROW_NUMBER here.
3. Split employees into three salary bands with NTILE(3) and show the band boundaries.