SQL RANK Function: RANK vs DENSE_RANK vs ROW_NUMBER Guide

In business analytics and data engineering, ranking items is one of the most common tasks: finding the top 3 salespeople in every branch, surfacing the best-selling products per category, or identifying repeat customers with the highest lifetime value. Understanding how SQL RANK works, and how it differs from DENSE_RANK and ROW_NUMBER, is a staple of technical SQL interviews.

In this tutorial, connecting to core concepts in what is SQL and our SQL window functions guide, we analyze the SQL RANK function, examine concrete side-by-side examples with tied values, and demonstrate how to write clean Top-N queries using CTEs.

What is the SQL RANK Function?

The SQL RANK function evaluates rows across a specified "window" of data without collapsing individual records into aggregate rows:

SELECT 
    employee_name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS global_salary_rank
FROM employees;

Syntax breakdown:

• RANK(): The ranking function. It takes no arguments.
• OVER (...): Defines the window frame.
• PARTITION BY (optional): Divides records into independent subsets (like departments or regions).
• ORDER BY: Dictates the sorting criteria on which the ranking sequence is determined.

SQL RANK vs DENSE_RANK vs ROW_NUMBER

To see the exact differences, observe how all three window functions evaluate a student examination score table with duplicate scores:

-- Sample Data:
-- student_name | score
-- Alice        | 98
-- Bob          | 92
-- Charlie      | 92
-- David        | 85
-- Eve          | 80
 
SELECT 
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM exam_scores;

Output Evaluation:

Advanced SQL RANK Examples: PARTITION BY and Top-N Filtering

1. Partitioning by Group (e.g., Department or Country)

When you include PARTITION BY, the SQL RANK sequence resets to 1 for every distinct group:

SELECT 
    employee_name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS dept_salary_rank
FROM employees;

Each department receives its own independent rank 1, 2, 3...

2. The Top-N Query Pattern Using CTEs

A frequent interview trap asks candidates to retrieve the "top 2 earners per department". If you write:

-- INVALID QUERY: Syntax Error!
SELECT employee_name, department, salary
FROM employees
WHERE RANK() OVER (PARTITION BY department ORDER BY salary DESC) <= 2;

This query fails immediately because window functions are calculated after the WHERE clause runs. To filter by SQL RANK, wrap the window calculation in a Common Table Expression (CTE):

-- VALID & PERFORMANT TOP-N PATTERN
WITH ranked_employees AS (
    SELECT 
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT 
    employee_name,
    department,
    salary,
    salary_rank
FROM ranked_employees
WHERE salary_rank <= 2
ORDER BY department, salary_rank;

For more details on query evaluation lifecycles, see our guide on the order of execution in SQL.

Advanced Analytical Patterns with SQL RANK

The true power of the sql rank function emerges when combined with Common Table Expressions (CTEs), multi-level partitioning, and moving window frames to solve complex business questions. Explore more guides in our SQL Tutorials hub.

Finding Top-3 Performers per Category with CTE Filtering

A frequent business request is finding the top 3 selling products within each regional category:

WITH RankedProducts AS (
    SELECT 
        category_name,
        product_name,
        total_sales,
        RANK() OVER (
            PARTITION BY category_name 
            ORDER BY total_sales DESC
        ) AS sales_rank
    FROM product_category_sales
)
SELECT 
    category_name,
    sales_rank,
    product_name,
    total_sales
FROM RankedProducts
WHERE sales_rank <= 3
ORDER BY category_name, sales_rank;

Because window functions cannot be evaluated directly inside a WHERE clause (due to SQL's order of execution), wrapping the query in a CTE computes the rankings first, enabling downstream filtering on sales_rank <= 3.

Dense Ranking vs Percentage Ranking (PERCENT_RANK and CUME_DIST)

In addition to discrete integer ranking, SQL provides statistical cumulative distribution functions:

• PERCENT_RANK(): Evaluates the relative percentile rank of a row between 0.0 and 1.0: PERCENT_RANK = (RANK - 1) / (Total Rows - 1)
• CUME_DIST(): Measures cumulative distribution (the fraction of rows with values less than or equal to the current row's value).
• NTILE(buckets): Splits ranked rows into equal quantiles (e.g., NTILE(4) divides customers into quartiles; NTILE(10) creates deciles for credit scoring).

SELECT 
    customer_id,
    lifetime_spend,
    RANK() OVER (ORDER BY lifetime_spend DESC) AS raw_rank,
    DENSE_RANK() OVER (ORDER BY lifetime_spend DESC) AS dense_rnk,
    NTILE(4) OVER (ORDER BY lifetime_spend DESC) AS spend_quartile,
    ROUND(PERCENT_RANK() OVER (ORDER BY lifetime_spend DESC)::NUMERIC, 3) AS percentile
FROM customer_ltv;

Real-World Business Scenario: Customer Re-Engagement RFM Scoring

In customer relationship management (CRM) analytics, ranking functions are combined with quantiles to identify VIP customers and churn risks:

WITH customer_aggregates AS (
    SELECT 
        customer_id,
        CURRENT_DATE - MAX(order_date) AS days_since_last_order,
        COUNT(order_id) AS lifetime_orders,
        SUM(order_total) AS total_monetary_spend
    FROM orders
    GROUP BY customer_id
)
SELECT 
    customer_id,
    days_since_last_order,
    RANK() OVER (ORDER BY total_monetary_spend DESC) AS spend_rank,
    NTILE(5) OVER (ORDER BY total_monetary_spend DESC) AS monetary_quintile,
    NTILE(5) OVER (ORDER BY days_since_last_order ASC) AS recency_quintile
FROM customer_aggregates;

Handling NULL Values in SQL RANK

In SQL sorting, NULL values can disrupt your ranking order if not explicitly controlled:

-- In PostgreSQL/Oracle, NULLs sort FIRST by default in DESC ordering.
-- Use NULLS LAST to force NULL values to the bottom:
SELECT 
    sales_rep_name,
    quota_attainment_pct,
    RANK() OVER (
        ORDER BY quota_attainment_pct DESC NULLS LAST
    ) AS attainment_rank
FROM sales_performance;

Check out our complete overview of SQL for Data Analyst for advanced real-world analytics workflows.

Summary Checklist for SQL RANK

• Use RANK() when tied rows should skip subsequent rank values.
• Use DENSE_RANK() when you want continuous ranks without gaps.
• Use ROW_NUMBER() when every record requires a distinct integer.
• Always wrap ranking queries in a CTE or subquery to filter by rank (WHERE rank <= N).
• Explicitly specify NULLS LAST to prevent unassigned values from capturing Rank 1.

SQL RANK in Cohort Retention and Customer Milestones

Beyond competitive leaderboards, senior analysts utilize the sql rank function to map customer lifecycle milestones:

• Flagging Milestone Purchases: By ranking transactions per customer chronologically (ORDER BY order_date ASC), analysts isolate customer order 1 (acquisition), order 2 (activation/retention), and order 5 (loyalty tier).
• Calculating Days Between Milestone Orders: Joining the ranked order table to itself allows measuring median days between first and second purchase, providing product marketing teams with the exact window for automated re-engagement email drips.

Explore our comprehensive SQL Tutorials hub and practice ranking queries on Topfolio Practice.

Dynamic Tie-Breaker Strategies in SQL RANK

When business rules require deterministic, non-arbitrary rank order even when primary metrics tie:

1. Multi-Column ORDER BY within OVER():

   sql

   Copy code

   SELECT 
       student_name,
       score,
       submission_time,
       RANK() OVER (
           ORDER BY score DESC, submission_time ASC
       ) AS tie_broken_rank
   FROM exam_submissions;

   Adding submission_time ASC guarantees that if two students score 98%, the student who submitted earlier receives the higher rank.
2. Deterministic ROW_NUMBER Fallback: In financial trading leaderboards or queue processing where ties are forbidden, ROW_NUMBER() OVER (ORDER BY score DESC, account_id ASC) ensures every single record receives a unique sequential integer from 1 to $N$.

Checklist for Using SQL RANK in Production Queries

Before deploying analytical ranking queries to production pipelines or business intelligence data models, verify this technical checklist:

• Have you chosen the correct ranking variant? Use RANK() if ties should skip ranks (1, 2, 2, 4), DENSE_RANK() if ranks must remain contiguous without gaps (1, 2, 2, 3), and ROW_NUMBER() for unique sequential pagination keys.
• Did you include secondary tie-breaker columns in your ORDER BY clause inside OVER() to guarantee deterministic query outputs?
• Remember that window functions cannot be filtered in WHERE; always wrap your ranking calculation inside a Common Table Expression (CTE) or derived table before applying threshold conditions like WHERE rank <= 10.

Related SQL Tutorials

• What Is SQL? The Complete Beginner to Pro Guide
• SQL Joins Explained with Practical Examples
• SQL Window Functions Guide
• SQL Cheat Sheet for Analysts
• Explore All Guides in the SQL Tutorials Hub