Home/Learn/SQL/ROW_NUMBER, RANK & DENSE_RANK

ROW_NUMBER, RANK & DENSE_RANK

Intermediate

Window Functions

ROW_NUMBER assigns a unique sequential integer to every row; RANK skips positions after ties; DENSE_RANK never skips — choose based on whether gaps in ranking matter.

Overview

All three functions require ORDER BY inside OVER(). ROW_NUMBER always produces distinct values regardless of ties, making it safe for pagination. RANK mimics sports ranking — two rows tied for 1st both get rank 1, and the next rank is 3. DENSE_RANK also gives tied rows the same rank, but the next rank is always the immediate next integer. A classic interview pattern is "top-N per group": partition by a category, rank by a metric, then filter in a CTE for rank <= N. You cannot filter on the computed rank in the same query's WHERE — a CTE or subquery is required.

Comparing the Three Ranking Functions

The only difference between the three functions is how they handle ties. Run them side-by-side on the same ORDER BY to see the gap clearly.

SQL — ROW_NUMBER vs RANK vs DENSE_RANK side-by-side

-- Show all three on employees ordered by salary within department
SELECT
    name,
    department_id,
    salary,
    ROW_NUMBER()  OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK()        OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK()  OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- Sample output for one department (two employees tied at 90000):
-- name     dept  salary  row_num  rnk  dense_rnk
-- Alice    1     90000   1        1    1
-- Bob      1     90000   2        1    1
-- Carol    1     75000   3        3    2   ← RANK skips to 3, DENSE_RANK goes to 2

Top-N Per Group Pattern

Find the top-N rows within each partition by wrapping the window function in a CTE and filtering on the rank. Use ROW_NUMBER when you need exactly N rows (no ties), DENSE_RANK when you want all tied rows at position N to be included.

SQL — top-N per group with DENSE_RANK

-- Top 2 highest-paid employees per department
WITH ranked_employees AS (
    SELECT
        e.name,
        d.name   AS department,
        e.salary,
        DENSE_RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS sal_rank
    FROM employees e
    JOIN departments d ON d.id = e.department_id
)
SELECT name, department, salary, sal_rank
FROM ranked_employees
WHERE sal_rank <= 2;

-- Rank products by total units sold within each category
WITH product_sales AS (
    SELECT
        p.name,
        p.category,
        SUM(o.quantity) AS total_sold,
        RANK() OVER (PARTITION BY p.category ORDER BY SUM(o.quantity) DESC) AS sales_rank
    FROM products p
    JOIN orders o ON o.product_id = p.id
    GROUP BY p.id, p.name, p.category
)
SELECT * FROM product_sales WHERE sales_rank <= 3;

Pagination with ROW_NUMBER

ROW_NUMBER is the standard way to implement keyset-style pagination or OFFSET-free page navigation. Assign row numbers over a deterministic ORDER BY, then filter by the desired page range.

SQL — ROW_NUMBER for pagination

-- Page 3 of 20 rows per page (rows 41-60), ordered by created_at
WITH paginated AS (
    SELECT
        id,
        name,
        email,
        created_at,
        ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS rn
    FROM users
)
SELECT id, name, email, created_at
FROM paginated
WHERE rn BETWEEN 41 AND 60;

Key Points to Remember

1ROW_NUMBER always produces unique sequential numbers — no two rows share the same value even on ties.
2RANK gives tied rows the same number and skips the next rank(s) equal to the number of tied rows.
3DENSE_RANK gives tied rows the same number but never skips — consecutive ranks are always n and n+1.
4The top-N per group pattern: partition by group, order by metric, filter rank <= N inside a CTE or subquery.
5Use ROW_NUMBER for pagination because uniqueness guarantees exactly one row per number.
6All three functions require ORDER BY inside OVER(); the result is undefined (non-deterministic) without it.

Interview Questions

What is the difference between RANK and DENSE_RANK? Give an example with ties.

MediumFlipkart

Write a query to find the top 3 products by revenue in each category.

MediumAmazon

Why is ROW_NUMBER preferred over OFFSET for pagination on large tables?

HardNetflix

Can you use RANK in a WHERE clause directly? Why or why not?

MediumGoogle

Ask Aria about ROW_NUMBER, RANK & DENSE_RANK

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Window Function Basics

LEAD & LAG