The Three Ranking Functions

Visual Comparison

Notice how ROW_NUMBER assigns unique numbers even to ties, RANK skips to 4 after the tie at 2, and DENSE_RANK stays sequential.

Core Syntax

-- All three ranking functions side by side
SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_val
FROM employees;

ROW_NUMBER — Unique Ordering

-- Select the most recent order per customer
WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        order_amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
)
SELECT customer_id, order_id, order_date, order_amount
FROM ranked_orders
WHERE rn = 1;

-- Deduplicate: keep only the latest record per student
WITH deduplicated AS (
    SELECT
        student_id,
        course_name,
        grade,
        submission_date,
        ROW_NUMBER() OVER (
            PARTITION BY student_id, course_name
            ORDER BY submission_date DESC
        ) AS rn
    FROM student_submissions
)
SELECT student_id, course_name, grade, submission_date
FROM deduplicated
WHERE rn = 1;

RANK — Gaps After Ties

-- Competition results with RANK (ties share rank, gaps follow)
SELECT
    contestant_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS competition_rank
FROM contest_results
ORDER BY competition_rank;

-- Output:
-- Alice    | 98 | 1
-- Bob      | 95 | 2
-- Charlie  | 95 | 2
-- Diana    | 92 | 4   <-- Gap after the tie at rank 2

DENSE_RANK — No Gaps

-- Grade assignment with DENSE_RANK (no gaps after ties)
SELECT
    student_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS grade_rank,
    CASE
        WHEN DENSE_RANK() OVER (ORDER BY score DESC) <= 3 THEN 'Honors'
        WHEN DENSE_RANK() OVER (ORDER BY score DESC) <= 7 THEN 'Pass'
        ELSE 'Needs Improvement'
    END AS designation
FROM student_scores;

Practical Scenario: Top N Per Group

-- Top 3 earners in each department
WITH department_ranks AS (
    SELECT
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS dept_rank
    FROM employees
)
SELECT employee_name, department, salary, dept_rank
FROM department_ranks
WHERE dept_rank <= 3
ORDER BY department, dept_rank;

-- Bottom 2 performers per department
WITH bottom_performers AS (
    SELECT
        employee_name,
        department,
        performance_score,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY performance_score ASC
        ) AS bottom_rank
    FROM employees
    WHERE performance_score IS NOT NULL
)
SELECT employee_name, department, performance_score
FROM bottom_performers
WHERE bottom_rank <= 2;

When to Use Each

Combining with Other Window Functions

-- Full salary analysis with ranking and comparison
SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_val,
    LAG(salary)  OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS gap_to_prev
FROM employees
ORDER BY department, row_num;