SQL RANK vs DENSE_RANK vs ROW_NUMBER: Practical Guide with Examples

When querying databases for analytical reports, executive leaderboards, or technical interview assessments, assigning relative order to rows is a daily necessity. SQL provides three primary window functions for this task: ROW_NUMBER(), RANK(), and DENSE_RANK().

While all three functions evaluate an OVER (ORDER BY ...) clause to compute integer rankings, their handling of identical (tied) values differs significantly. Choosing the wrong function leads to subtle reporting bugs, missing records in Top-N analyses, and non-deterministic query results.

This guide provides a practical, evidence-based breakdown of all three ranking functions, using a reproducible sample dataset, clear output walkthroughs, department-level partitioning, and realistic interview scenarios.

---

Direct Answer: How the Three SQL Ranking Functions Differ

The core difference between ROW_NUMBER(), RANK(), and DENSE_RANK() is strictly how they handle duplicate values in the ORDER BY column:

• `ROW_NUMBER()`: Assigns a sequential, unique integer starting at 1 to every row within a partition. It never produces duplicate ranks and never skips numbers, even when values tie.
• `RANK()`: Assigns identical rank numbers to rows with equal values. It skips subsequent ranks by the number of ties (e.g., if two rows tie for 1st, ranks are 1, 1, 3).
• `DENSE_RANK()`: Assigns identical rank numbers to rows with equal values, but never skips numbers. Subsequent distinct values receive the next consecutive integer (e.g., if two rows tie for 1st, ranks are 1, 1, 2).

---

Comparison Table: Ranking Behavior at a Glance

---

The Reproducible Employee Dataset

To observe how each function behaves in practice, consider an employees table containing intentional salary ties within the Engineering and Marketing departments:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice Chen',     'Engineering', 120000),
(2, 'Bob Smith',      'Engineering', 120000),
(3, 'Charlie Davis',  'Engineering', 110000),
(4, 'Diana Prince',   'Engineering',  95000),
(5, 'Evan Wright',    'Engineering',  95000),
(6, 'Fiona Gallagher','Engineering',  80000),
(7, 'George Clark',   'Marketing',   105000),
(8, 'Hannah Abbott',  'Marketing',    90000),
(9, 'Ian Malcolm',    'Marketing',    90000),
(10, 'Julia Roberts', 'Marketing',    75000);

Notice the specific ties present in our data:

• In Engineering, Alice and Bob both earn \$120,000 (tied for top salary).
• Diana and Evan both earn \$95,000 (tied for fourth-highest salary).
• In Marketing, Hannah and Ian both earn \$90,000.

---

Side-by-Side Query: Evaluating All Three Functions

Let us run a single query on the Engineering department (department = 'Engineering') to observe ROW_NUMBER(), RANK(), and DENSE_RANK() side by side:

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC, id ASC;

Result Table

Why Did `RANK()` Skip Numbers?

Notice line 3 (Charlie Davis):

• Two employees (Alice and Bob) occupied rank 1.
• Because two preceding rows shared position 1 and position 2, the next row is at index position 3.
• Therefore, RANK() assigned 3, leaving rank 2 completely empty.
• Meanwhile, DENSE_RANK() only tracked distinct salary thresholds: \$120k is Tier 1, \$110k is Tier 2, \$95k is Tier 3, and \$80k is Tier 4.

---

Group Rankings Using `PARTITION BY`

In enterprise reporting, you rarely rank an entire company in one global list. Instead, you rank employees within each department, products within each category, or transactions within each customer account.

The PARTITION BY clause divides the result set into distinct partitions. The window function resets its ranking computation back to 1 at the start of each new partition:

SELECT 
    department,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_dense_rnk
FROM employees
ORDER BY department ASC, salary DESC, id ASC;

Output with Partition Resets

Notice that when the department switches from Engineering to Marketing, all three functions reset back to 1. In Marketing, Hannah and Ian tie at \$90,000, so Julia receives rank 4 under RANK() (skipping 3), but rank 3 under DENSE_RANK().

---

How to Select the Top 3 Employees per Department

A classic interview and reporting requirement is: *"Retrieve the top 3 highest-earning employees in each department."*

Why You Cannot Use `WHERE` Directly

A frequent beginner mistake is attempting to filter by the window function in the WHERE clause:

-- INVALID QUERY: THIS WILL FAIL WITH A SYNTAX ERROR
SELECT department, name, salary
FROM employees
WHERE DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) <= 3;

The Standard Solution: Common Table Expression (CTE)

To filter on ranking results, calculate the ranking inside a CTE or subquery, then filter the resulting column in an outer query:

WITH ranked_staff AS (
    SELECT 
        department,
        name,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS salary_tier
    FROM employees
)
SELECT 
    department,
    name,
    salary,
    salary_tier
FROM ranked_staff
WHERE salary_tier <= 3
ORDER BY department ASC, salary_tier ASC, name ASC;

Result Analysis: How Function Choice Changes Top-N

Depending on whether you use ROW_NUMBER(), RANK(), or DENSE_RANK() in salary_tier <= 3, the records returned differ substantially:

1. With `ROW_NUMBER() <= 3`: Returns strictly 3 rows per department. In Engineering, Alice, Bob, and Charlie are returned. Diana and Evan (\$95k) are omitted.
2. With `RANK() <= 3`: Returns employees whose rank is 1, 2, or 3. In Engineering, Alice (1), Bob (1), and Charlie (3) are returned (3 rows). Because Charlie is rank 3, rank 2 was skipped. Diana and Evan are rank 4, so they are excluded.
3. With `DENSE_RANK() <= 3`: Returns all employees in the top 3 salary tiers. In Engineering, Tier 1 (\$120k: Alice, Bob), Tier 2 (\$110k: Charlie), and Tier 3 (\$95k: Diana, Evan) are all returned (5 rows total).

Modern SQL Dialect Alternative: The `QUALIFY` Clause

If you are using Snowflake, Google BigQuery, Databricks, or Teradata, you can eliminate the CTE boilerplate using the QUALIFY clause:

-- Supported in Snowflake, BigQuery, Databricks
SELECT 
    department,
    name,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_tier
FROM employees
QUALIFY salary_tier <= 3
ORDER BY department, salary_tier;

Note: PostgreSQL, MySQL, and Microsoft SQL Server do not currently support QUALIFY, so the CTE pattern remains the universal standard.

---

Deterministic Ordering and Tie-Breaking

What happens when ROW_NUMBER() encounters tied values?

Consider Alice and Bob, who both earn \$120,000. If we execute:

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

Which employee gets rn = 1 and which gets rn = 2?

Without a secondary sorting column, SQL engines do not guarantee deterministic order. On PostgreSQL, the physical storage order on disk (heap page layout) may dictate the sequence. On parallel query planners or distributed databases (like Snowflake or Redshift), successive runs of the exact same query may invert Alice and Bob's row numbers!

The Production Fix: Secondary Tie-Breaker

To make row numbers 100% deterministic and reproducible across query executions, always append a unique identifier (such as the primary key id) to the ORDER BY clause:

-- 100% Deterministic: Alice (id=1) will always receive rn=1 before Bob (id=2)
SELECT 
    id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC, id ASC) AS deterministic_rn
FROM employees;

---

4 Common Mistakes and How to Avoid Them

1. Forgetting That Ranking Window Functions Reject Window Frames

Aggregate window functions like SUM() or AVG() allow frame definitions such as ROWS BETWEEN 1 PRECEDING AND CURRENT ROW. Ranking functions do not:

-- SYNTAX ERROR in PostgreSQL, SQL Server, MySQL
SELECT name, RANK() OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM employees;

Ranking functions evaluate over the entire partition. Specifying a window frame throws a database engine syntax error.

2. Assuming `RANK()` Returns Consecutive Numbers

If you write downstream application code expecting ranks 1, 2, 3, 4 without gaps, RANK() will break your frontend components whenever a tie occurs. Always verify whether gap-free tiers (DENSE_RANK()) are required.

3. Missing `ORDER BY` in the Window Specification

Unlike COUNT(*) OVER (PARTITION BY department) which can operate on an un-ordered partition, ranking functions require an ORDER BY clause. Omitting it raises an immediate syntax error.

4. Overlooking NULL Values in Sorting

By default, SQL standards place NULL values differently depending on the dialect:

• PostgreSQL & Oracle: ORDER BY salary DESC puts NULL values first by default (NULLS FIRST).
• MySQL & SQL Server: ORDER BY salary DESC puts NULL values last by default (NULLS LAST).

To prevent unexpected ranks, explicitly state NULL precedence:

-- Explicit null placement for reliable cross-engine behavior
DENSE_RANK() OVER (ORDER BY salary DESC NULLS LAST)

---

Practical SQL Interview Questions & Solutions

Question 1: How do you find the second highest salary across an entire company?

Answer: Use DENSE_RANK() inside a CTE to find distinct salary tier 2. Using ROW_NUMBER() fails if multiple employees tie for the top salary.

WITH salary_tiers AS (
    SELECT 
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS tier
    FROM employees
)
SELECT DISTINCT salary
FROM salary_tiers
WHERE tier = 2;

Question 2: If three employees tie for 1st place and two employees tie for the next score, what will `RANK()` and `DENSE_RANK()` assign to the 6th employee?

Answer:

• Position 1, 2, 3: Tied employees all get Rank 1 (under both functions).
• Position 4, 5: The two employees tie at the next score.

- Under RANK(): Position 4 receives Rank 4 (ranks 2 and 3 skipped). Both receive Rank 4. - Under DENSE_RANK(): Consecutive tier 2 is assigned. Both receive Dense Rank 2.

• Position 6: The 6th employee has 5 preceding records.

- Under RANK(): Receives Rank 6 (rank 5 skipped). - Under DENSE_RANK(): Receives Dense Rank 3 (strictly consecutive).

Question 3: How do you deduplicate a staging table, keeping only the most recently updated record for each user?

Answer: Partition by user_id, order by updated_at DESC, id DESC, and delete rows where ROW_NUMBER() > 1.

WITH deduplicated AS (
    SELECT 
        id,
        user_id,
        email,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY updated_at DESC, id DESC
        ) AS row_num
    FROM staging_users
)
DELETE FROM staging_users
WHERE id IN (
    SELECT id FROM deduplicated WHERE row_num > 1
);

---

Hands-On Practice Exercises

Test your understanding with these practical query challenges:

Exercise 1: Department Salary Spread

Write a query displaying each employee's name, department, salary, and the difference between their salary and the highest salary in their department, using window functions.

-- Solution:
SELECT 
    name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) - salary AS salary_gap_from_top
FROM employees
ORDER BY department, salary DESC;

Exercise 2: Identify Tied Winners

Write a query to find all employees who share the #1 rank in their department. If a department has a sole top earner, return only that person; if multiple tie, return all tied earners.

-- Solution:
WITH ranked_staff AS (
    SELECT 
        department,
        name,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT department, name, salary
FROM ranked_staff
WHERE rnk = 1
ORDER BY department, name;

---

Next Steps: Practice in Pathubs' Interactive SQL Lab

Reading SQL explanations is essential, but building intuition requires testing queries with live data:

1. [Interactive SQL DENSE_RANK Lab](/resource/sql-dense-rank): Explore our browser-based visualizer where you can adjust student scores and employee salaries with live sliders and inspect real-time reactive calculations across all three window functions.
2. [Data Analytics Career Roadmap](/roadmap/data-analytics): Follow our complete curriculum covering relational database foundations, CTEs, aggregation techniques, window functions, and BI dashboard reporting.
3. [SQL Left and Right Joins Deep Dive](/resource/sql-left-right-join): Master outer join mechanics, row multiplication risks, and null-safe data transformation.
4. [Platform Career Comparison](/compare): Explore how SQL mastery bridges data analysts, analytics engineers, and backend developers across salary benchmarks and required competencies.