ROW_NUMBER vs RANK vs DENSE_RANK in SQL (Tie Examples)

In technical SQL interviews and day-to-day analytics, ranking questions are everywhere: "Find the top 3 highest-priced products per category," "Deduplicate user logs keeping only the latest event," and "Find the second-highest salary in each department."

Candidates often stumble not because they don't know the syntax, but because they pick the wrong ranking function or fail to account for ties. Choosing RANK() when you need ROW_NUMBER() can accidentally return duplicates, while using ROW_NUMBER() without a tiebreaker can produce non-deterministic results that change between runs.

This guide breaks down the mechanics of ROW_NUMBER(), RANK(), and DENSE_RANK(), visualizes their tie-handling side by side, and demonstrates the classic production deduplication pattern using a real e-commerce PostgreSQL dataset with 2,000 orders and 800 reviews. For broader window function coverage, read our SQL Window Functions Guide and SQL CTE Guide.

1. The Core Idea: The OVER() Clause

Standard GROUP BY collapses multiple rows into a single summary row, destroying row-level details like product names or customer IDs. Window functions solve this by computing calculations across a set of rows while keeping every individual row intact.

The keyword that activates window calculations is OVER().

Preserving Detail with an Empty OVER() Clause

An empty OVER() clause treats the entire table as a single window frame, stamping the grand total on every single row:

SELECT order_id,
       total_amount,
       ROUND(SUM(total_amount) OVER (), 2) AS grand_total_revenue
FROM orders
ORDER BY order_id
LIMIT 5;

The Output

GROUP BY would have collapsed all 2,000 orders into 1 summary row. The window function appends the ₹782,905.04 total alongside each order while preserving all 2,000 individual records.

2. Visualizing Ties: ROW_NUMBER vs RANK vs DENSE_RANK

When ranking rows within a partition, what happens when two or more rows have identical values? Let's compare all three functions on the same dataset.

SCENARIO: Ranking 4 candidates by score (Scores: 100, 90, 90, 80)

┌────────────────┬───────┬────────────┬────────┬────────────┐
│ Candidate      │ Score │ ROW_NUMBER │ RANK() │ DENSE_RANK │
├────────────────┼───────┼────────────┼────────┼────────────┤
│ Sarah          │ 100   │     1      │   1    │     1      │
│ Alex (Tie)     │  90   │     2      │   2    │     2      │
│ Rohan (Tie)    │  90   │     3      │   2    │     2      │
│ Priya          │  80   │     4      │   4    │     3      │
└────────────────┴───────┴────────────┴────────┴────────────┘
                              │           │           │
                     Unique sequential  Leaves gap   No gaps
                        (1,2,3,4)       (1,2,2,4)   (1,2,2,3)

Real E-Commerce Example: Ranking Products in the Books Category

In our products table, the top product in the Books category is Advanced Set 129 at ₹497.19. Further down the list, two distinct book products share the exact same price: ₹447.90.

Let's run RANK(), DENSE_RANK(), and ROW_NUMBER() side by side:

SELECT category,
       product_name,
       price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
       RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rnk
FROM products
WHERE category = 'Books'
ORDER BY price DESC
LIMIT 7;

The Live Output

The Critical Differences

1. The Tie at ₹447.90: Both Basic Bundle 904 and Pro System 512 receive rank 4 in both RANK() and DENSE_RANK().
2. The Subsequent Row (Premium Widget 721):
   • RANK() jumps straight to 6, skipping 5. It accounts for the 2 items tied at rank 4.
   • DENSE_RANK() assigns 5, closing the gap.
   • ROW_NUMBER() arbitrarily numbered them 4 and 5 sequentially.

3. The Production Deduplication Pattern: ROW_NUMBER + rn = 1

The single most common real-world use case for ROW_NUMBER() is deduplicating data—keeping only the latest or most complete row per business key.

The Problem: Repeat Customer Reviews

In our reviews table, there are 800 total reviews, but only 735 distinct (customer_id, product_id) pairs. That means 65 rows represent repeat reviews submitted by the same customer for the same product.

Our business goal: Retain only the most recent review for every customer-product pair.

Step 1: Assigning Row Numbers with a Tiebreaker

SELECT review_id,
       customer_id,
       product_id,
       rating,
       review_date,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id, product_id
           ORDER BY review_date DESC, review_id DESC
       ) AS rn
FROM reviews;

Step 2: Filtering with a Common Table Expression (CTE)

Because window functions cannot be evaluated directly inside a WHERE clause (due to SQL query execution order), wrap the ranking query in a CTE:

WITH ranked_reviews AS (
    SELECT review_id,
           customer_id,
           product_id,
           rating,
           review_date,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id, product_id
               ORDER BY review_date DESC, review_id DESC
           ) AS rn
    FROM reviews
)
SELECT review_id,
       customer_id,
       product_id,
       rating,
       review_date
FROM ranked_reviews
WHERE rn = 1
ORDER BY review_id;

The Result

• Input Table: 800 reviews
• Output Table: Exactly 735 clean, deduplicated rows
• Discarded: 65 outdated duplicate reviews

4. Top-N per Group: Choosing Between RANK and DENSE_RANK

Suppose management asks: "Give me the 3 highest distinct product prices in each category."

If you use ROW_NUMBER(), categories with price ties will return fewer than 3 distinct price tiers. If you use RANK(), a tie at rank 2 skips rank 3, causing a WHERE rnk <= 3 filter to miss valid items.

DENSE_RANK() is the mathematically correct tool for Top-N distinct benchmark tiers.

WITH category_price_ranks AS (
    SELECT category,
           product_name,
           price,
           DENSE_RANK() OVER (
               PARTITION BY category
               ORDER BY price DESC
           ) AS price_tier
    FROM products
    WHERE category IS NOT NULL
)
SELECT category,
       product_name,
       price,
       price_tier
FROM category_price_ranks
WHERE price_tier <= 3
ORDER BY category, price_tier, price DESC;

This guarantees every category displays all products belonging to the top 3 highest price tiers, regardless of how many items share identical prices.

5. Running Totals with SUM() OVER (ORDER BY)

Window functions are not limited to rankings. Adding an ORDER BY inside OVER() transforms basic aggregates into cumulative running calculations.

Cumulative Monthly Revenue

Let's compute monthly revenue alongside the cumulative running total across time:

WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', order_date)::date AS month,
           SUM(total_amount)                     AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY month
)
SELECT month,
       ROUND(revenue, 2)                            AS monthly_rev,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS cumulative_revenue
FROM monthly_revenue
ORDER BY month;

The Output

The OVER (ORDER BY month) frame automatically defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, smoothly accumulating revenue from the first recorded month forward.

6. Feature Comparison Matrix

7. Key Takeaways & Practice

1. Pick ROW_NUMBER() whenever you need a single distinct winner (such as deduplication or strict pagination). Always include a secondary unique tiebreaker column in ORDER BY.
2. Pick DENSE_RANK() when ranking items into tiers (e.g. 1st, 2nd, and 3rd highest salary) where tied entities should not consume rank numbers.
3. Pick RANK() when building traditional competition leaderboards where two 2nd-place finishers mean the next participant is 4th.
4. Wrap in a CTE whenever you need to filter ranking output with WHERE.

Test your window function skills with interactive datasets in the SQL Window Functions Practice Sandbox or prepare for technical interviews with the Topfolio Data Analyst Track. To master query readability and chaining, check out our SQL CTE Guide.